1 / 30

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?. A “waterfall” is the term used to show how records in a targeted population fall out of the population.

Sophia
Download Presentation

Automation Of An Audit Waterfall

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Automation Of An Audit Waterfall Creating An Audit Waterfall For Clients Using Macros And ODS tagset.excelxp By Denise A. Kruse SAS Contractor

  2. 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.

  3. 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

  4. Why Automate A Waterfall? • Time savings! • Time savings! • Time savings! Plus, you may get to learn some new code.

  5. Example Output

  6. 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

  7. 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);

  8. Inside The Table WRPT_FINAL

  9. 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);

  10. 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;

  11. 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

  12. 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;

  13. Visual Of multi_waterfall.sas Calculation

  14. Code Inside multi_waterfall.sas data skippy; format y $100.; merge wtitles wrpt2; run;

  15. 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');

  16. 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;

  17. Close Out The ODS ods tagsets.ExcelXP close; odslisting ; run;

  18. Adding Tabs To The Worksheet ods tagsets.ExcelXP options(sheet_name='SAMPLE' embedded_titles='No' embedded_footnotes='No'); procprintdata = &sample.(obs=&s_cnt.) ; run ;

  19. 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 ;

  20. 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;

  21. 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;

  22. Footer Amount ***WATERFALL - TOTAL ELIGIBLE FOOTER***; data_null_; if0thenset <dataset name> nobs=nobs; put nobs=; call symputx('eligible',put(nobs,comma11.)); stop; run;

  23. 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;

  24. Sample Tab

  25. FREQS Tab

  26. 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

  27. 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;

  28. 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;

  29. 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.

  30. 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

More Related