300 likes | 897 Views
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.
E N D
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. Think of it as the business owner’s program log and statistics.
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? • Time savings! • Time savings! • Time savings! Plus, you may get to learn some new code.
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 %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 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 ***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 data_null_; if0thensetwtitles nobs=nobs; put nobs=; call symputx('cnt',(nobs)); stop; run; snippet from 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.sas data skippy; format y $100.; merge wtitles wrpt2; run;
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 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 ods tagsets.ExcelXP close; odslisting ; run;
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 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 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 ****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 ***WATERFALL - TOTAL ELIGIBLE FOOTER***; data_null_; if0thenset <dataset name> nobs=nobs; put nobs=; call symputx('eligible',put(nobs,comma11.)); stop; run;
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 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
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;
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 • 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 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