automation of an audit waterfall n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Automation Of An Audit Waterfall PowerPoint Presentation
Download Presentation
Automation Of An Audit Waterfall

Loading in 2 Seconds...

play fullscreen
1 / 30

Automation Of An Audit Waterfall - PowerPoint PPT Presentation


  • 521 Views
  • Uploaded on

Automation Of An Audit Waterfall. Creating An Audit Waterfall For Clients Using Macros And ODS tagset.excelxp By Denise A. Kruse SAS Contractor. What Is A Waterfall?. A “waterfall” is the term used to show how records in a targeted population fall out of the population.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Automation Of An Audit Waterfall' - Sophia


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
automation of an audit waterfall

Automation Of An Audit Waterfall

Creating An Audit Waterfall For Clients Using Macros And ODS tagset.excelxp

By Denise A. Kruse

SAS Contractor

what is a waterfall
What Is A Waterfall?

A “waterfall” is the term used to show how records in a targeted population fall out of the population.

Think of it as the business owner’s program log and statistics.

why waterfall
Why Waterfall?
  • Keeps the programmer and business client in sync with the data
  • Enables the business client to be more independent when questions arise
  • Prevents the programmer from spending unnecessary time on high level questions from the business client
why automate a waterfall
Why Automate A Waterfall?
  • Time savings!
  • Time savings!
  • Time savings!

Plus, you may get to learn some new code.

design
Design

Create two modules that will be include statements for the main program.

  • waterfall_macro.sas

This module takes the number of observations from each dataset and saves the number as one row in a table.

2.multi_waterfall.sas

This module creates the actual output of a multi-sheet Excel spreadsheet

code for waterfall macro sas
Code For waterfall_macro.sas

%macro ds_vol(lib, dsn);

data _null_;

if 0 then set &lib..&dsn. nobs=nobs;

put nobs=;

call symputx('cnt',nobs);

stop;

run;

data wrpt;

x=&cnt.;

run;

%if %sysfunc(exist(wrpt_final))=1 %then %do;

proc append base=wrpt_final

data=wrpt FORCE;

run;

%end;

%else %do;

data wrpt_final;

set wrpt;

run;

%end;

%mend ds_vol;

INPUT: library.datasetname

Example: %ds_vol(work,mydata); %ds_vol(perm,mydata);

example code for the main program
Example Code For The Main Program

Insert the macro call after datasets are created….

data affil_zip cjv_flag exclude no_mail scrubbed;

set dee.match;

if affil_zip_flag = 1thenoutput affil_zip;

elseif cjv_flag = 1thenoutput cjv_flag;

elseif exclude_flag=1thenoutput exclude;

elseif dnm_flag=1thenoutput no_mail;

elseoutput scrubbed;

run;

****WATERFALL - ROW 2;

%ds_vol(work,affil_zip);

****WATERFALL - ROW 3;

%ds_vol(work,cjv_flag);

****WATERFALL - ROW 4;

%ds_vol(work,exclude);

****WATERFALL - ROW 5;

%ds_vol(work,no_mail);

creating the custom titles for the waterfall
Creating The Custom Titles For The Waterfall

***WATERFALL dataset ***;

***** keep this in same order as macro calls above;

datawtitles;

FORMAT Y $100.;

INPUT Y;

cards;

Not_in_targeted_CL_List

Active_Affiliate_Accounts_(affil_zip_flag)

CJV_(cjv_flag)

exclude_flag

DNM

Corporate_Exclusions

Duplicates_by_Hierarchy_Number

Bad_Address

run;

find number of rows in the waterfall report
Find Number Of Rows In The Waterfall Report

data_null_;

if0thensetwtitles nobs=nobs;

put nobs=;

call symputx('cnt',(nobs));

stop;

run;

snippet from multi_waterfall.sas

code inside multi waterfall sas
Code Inside multi_waterfall.sas

data wrpt2;

format remain1-remain&cnt. num prev 8. ;

length remain1-remain&cnt. num prev 8. ;

set wrpt_final;

retain remain1-remain&cnt. prev ;

if _n_ =1then do;

num=0; *initialize counter and array;

array remain (1:&cnt.) 8.;

remain(1) = &title1_amt. - x;

TOT_REMAIN=remain(1);

output;

num + 1;

end;

elsedo;

num + 1;

prev=num-1;

remain(num)= remain(prev) - x;

TOT_REMAIN=remain(num);

output;

end;

run;

code inside multi waterfall sas1
Code Inside multi_waterfall.sas

data skippy;

format y $100.;

merge wtitles wrpt2;

run;

coding with ods
Coding With ODS

odslistingclose;

run;

ods tagsets.ExcelXP file="/MyDir/waterfall/waterfall_&campaign..xls"

style=waterfall;

*options(doc='help' );

ods tagsets.ExcelXP options(sheet_name='Waterfall'

embedded_titles='Yes'

embedded_footnotes='Yes');

simple proc report
Simple PROC Report

procreportdata=skippy split='*'ls=256headline nowd style(header)={font_weight=bold just=center};

title1"Waterfall Report";

title2"Total Accounts (Universe) " &title1_amt2.;

footnote"Total Balance Leads (Eligible Accounts) " &eligible.;

column y

x

TOT_REMAIN;

define y / order=data 'Scrub';

define x / order=data style=numeric_data 'Amount';

define TOT_REMAIN / order=data style=numeric_data 'Remaining';

run;

close out the ods
Close Out The ODS

ods tagsets.ExcelXP close;

odslisting ;

run;

adding tabs to the worksheet
Adding Tabs To The Worksheet

ods tagsets.ExcelXP options(sheet_name='SAMPLE'

embedded_titles='No'

embedded_footnotes='No');

procprintdata = &sample.(obs=&s_cnt.) ;

run ;

adding tabs to the worksheet1
Adding Tabs To The Worksheet

ods tagsets.ExcelXP options(sheet_name='FREQS'

sheet_interval='None'

embedded_titles='No'

embedded_footnotes='No');

procfreqdata = &sample.;

table &freq_vars./ missing ;

run ;

customizing the format of the waterfall
Customizing The Format Of The Waterfall

ODSPATH work.templat(update) sasuser.templat(read)

sashelp.tmplmst(read);

proctemplate;

define style styles.waterfall;

parent = styles.sansprinter;

style numeric_data from data /

tagattr='format:#,##0;;'

just = right

;

end;

run;

title amount
Title Amount

****WATERFALL - TOTAL UNIVERSE;

data_null_;

if0thenset <dataset name> nobs=nobs;

put nobs=;

call symputx('title1_amt2',put(nobs,comma11.));

call symputx('title1_amt',nobs);

stop;

run;

footer amount
Footer Amount

***WATERFALL - TOTAL ELIGIBLE FOOTER***;

data_null_;

if0thenset <dataset name> nobs=nobs;

put nobs=;

call symputx('eligible',put(nobs,comma11.));

stop;

run;

frequency variables
Frequency Variables
  • data_null_;
  • freq_vars='dnc_flag cl_il_flag
        • campaign_clean_flag bad_debt_flag
        • exclude_flag acct_status_code
        • active_flag ban_decile
        • con_mnths_to_end_qty equip_offer
        • list_id pv_offer';
  • call symputx('freq_vars',freq_vars);
  • run;
review
Review

Two modules to include:

%include'/MyDir/pgm/waterfall_macro.sas';

This module takes the number of observations from each dataset and saves the number as one row in a table.

%include'/MyDir/pgm/multi_waterfall.sas‘

This module creates the actual output of a multi-sheet Excel spreadsheet

review1
Review

Call the macro from the main program as many times as necessary:

****WATERFALL - ROW 2;

%ds_vol(work,affil_zip);

Add code for the header and footer of the waterfall.

****WATERFALL - TOTAL UNIVERSE;

data_null_;

if0thenset <dataset name> nobs=nobs;

put nobs=;

call symputx('title1_amt2',put(nobs,comma11.));

call symputx('title1_amt',nobs);

stop;

run;

review2
Review

Customize the labels for the waterfall:

datawtitles;

FORMAT Y $100.;

INPUT Y;

cards;

Not_in_targeted_CL_List

Active_Affiliate_Accounts_(affil_zip_flag)

CJV_(cjv_flag)

exclude_flag

DNM

Corporate_Exclusions

Duplicates_by_Hierarchy_Number

Bad_Address

run;

conclusion
Conclusion
  • Creating an audit waterfall programmatically fulfills a business need and saves time. Without it a programmer needs to cut and paste, gather files and manually format the spreadsheet.
  • Today I have presented one way to reach the goal of automation. Be creative and do what works for you but, hopefully, some of the code I shared with you today will be part of your next process.
contact information
Contact Information

Denise A. Kruse

DeniseAKruse@gmail.com

678-457-3677

Thanks:

Bruce Johnson from SAS-L for help with proc template

References:

http://support.sas.com/rnd/base/ods/odsmarkup/p236-31.pdf Eric Gebhart from SAS