Transcript SAS Hash Object: My New Best Friend

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.
2
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
3
Why Automate A Waterfall?
• Time savings!
• Time savings!
• Time savings!
Plus, you may get to learn some new code.
4
Example Output
5
Design
Create two modules that will be include statements for the
main program.
1.
2.
waterfall_macro.sas
This module takes the number of observations from
each dataset and saves the number as one row in a
table.
multi_waterfall.sas
This module creates the actual output of a multi-sheet
Excel spreadsheet
6
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);
7
Inside The Table WRPT_FINAL
x
9,630,064
27,525
1,323
332,646
30,454
1,100,984
8
Example Code For The Main Program
Insert the macro call after datasets are created….
data affil_zip
cjv_flag
exclude
no_mail
set dee.match;
if affil_zip_flag = 1 then output affil_zip;
else if cjv_flag = 1 then output cjv_flag;
else if exclude_flag=1 then output exclude;
else if dnm_flag=1 then output no_mail;
else output scrubbed;
run;
scrubbed;
****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);
9
Creating The Custom Titles For The Waterfall
***WATERFALL dataset ***;
***** keep this in same order as macro calls above;
data wtitles;
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;
10
Find Number Of Rows In The Waterfall Report
data _null_;
if 0 then set wtitles nobs=nobs;
put nobs=;
call symputx('cnt',(nobs));
stop;
run;
snippet from
multi_waterfall.sas
Y
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
11
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_ =1 then 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;
else do;
num + 1;
prev=num-1;
remain(num)= remain(prev) - x;
TOT_REMAIN=remain(num);
output;
end;
run;
12
Visual Of multi_waterfall.sas Calculation
X
TOT_REMAIN
9,630,064
TITLE_AMT – X
27,525
PREV – X
PREV – X
1,323
332,646
PREV – X
30,454
PREV – X
1,100,984
PREV – X
PREV - X
13
Code Inside multi_waterfall.sas
data skippy;
format y $100.;
merge wtitles wrpt2;
run;
Y
X
Not_in_targeted_CL_List
9,630,064
Active_Affiliate_Accounts_(affil_zip_flag)
27,525
CJV_(cjv_flag)
exclude_flag
1,323
DNM
332,646
Corporate_Exclusions
30,454
Duplicates_by_Hierarchy_Number
1,100,984
Bad_Address
14
Coding With ODS
ods listing close;
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');
15
Simple PROC Report
proc report data=skippy split='*' ls=256 headline
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;
16
Close Out The ODS
ods tagsets.ExcelXP close;
ods listing ;
run;
17
Adding Tabs To The Worksheet
ods tagsets.ExcelXP
options(sheet_name='SAMPLE'
embedded_titles='No'
embedded_footnotes='No');
proc print data = &sample.(obs=&s_cnt.) ;
run ;
18
Adding Tabs To The Worksheet
ods tagsets.ExcelXP
options(sheet_name='FREQS'
sheet_interval='None'
embedded_titles='No'
embedded_footnotes='No');
proc freq data = &sample.;
table &freq_vars./ missing ;
run ;
19
Customizing The Format Of The Waterfall
ODS PATH work.templat(update)
sasuser.templat(read)
sashelp.tmplmst(read);
proc template;
define style styles.waterfall;
parent = styles.sansprinter;
style numeric_data from data /
tagattr='format:#,##0;;'
just = right
;
end;
run;
20
Title Amount
****WATERFALL - TOTAL UNIVERSE;
data _null_;
if 0 then set <dataset name> nobs=nobs;
put nobs=;
call
symputx('title1_amt2',put(nobs,comma11.));
call symputx('title1_amt',nobs);
stop;
run;
21
Footer Amount
***WATERFALL - TOTAL ELIGIBLE FOOTER***;
data _null_;
if 0 then set <dataset name> nobs=nobs;
put nobs=;
call
symputx('eligible',put(nobs,comma11.));
stop;
run;
22
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;
23
Sample Tab
24
FREQS Tab
25
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
26
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_;
if 0 then set <dataset name> nobs=nobs;
put nobs=;
call symputx('title1_amt2',put(nobs,comma11.));
call symputx('title1_amt',nobs);
stop;
run;
27
Review
Customize the labels for the waterfall:
data wtitles;
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;
28
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.
29
Contact Information
Denise A. Kruse
[email protected]
678-457-3677
Thanks:
Bruce Johnson from SAS-L for help with proc template
References:
http://support.sas.com/rnd/base/ods/odsmarkup/p23631.pdf Eric Gebhart from SAS
30