Hello,
I need help with a report that I am trying to build. Below is my sample data.
data yy;
Input empid $5. @6 tier_level $6. @12 current_month_pmt Last_month Future_month;
Infile cards;
cards;
18457 Tier1 215.35 408.13 410.23
12877 Tier1 715.35 1178.83 1120.95
17894 Tier1 115.35 678.57 0
86886 Tier2 0 6788.13 6810.23
54545 Tier2 115.08 278.78 320.11
21478 Tier2 895.04 0 785.57
25710 Tier2 235.45 2575.29 2879.23
25791 Tier3 98.25 550.83 623.87
99987 Tier3 0 8908.20 9902.13
;
Run;
Basically, I have three tier level of employees and I want to sum their last month, current month & next month
payments and count number of employees. I tried with proc tabulate but I am running with two challenges 1) i don't know how to eliminate an employee from count if they have "zero" payment 2) I couldn't get all 3 payment types in a single row(side by side)
I know it doesn't help much but here what i coded so far..
Proc tabulate data=yy S=[foreground=black just=c];
class tier_level;
var current_month_pmt;
table tier_level = '' all={label='Total' s=[just=C]}, ( (current_month_pmt='' * (n='# employees'*F=12. sum='$ Amount' * f=dollar18.) ) )
/misstext='0';
run;
Below is my desired output.Any help is appreciated! Thanks.
Current Month | Last Month | Next Month | ||||
# employees | $ Amount | # employees | $ Amount | # employees | $ Amount | |
Tier1 | 3 | $ 1,046.00 | 3 | $ 2,265.53 | 2 | $ 1,531.18 |
Tier2 | 3 | $ 1,245.57 | 3 | $ 9,642.20 | 4 | $ 10,795.14 |
Tier3 | 1 | $ 98.25 | 2 | $ 9,459.03 | 2 | $ 10,526.00 |
Total | 7 | $ 2,389.82 | 8 | $ 21,366.76 | 8 | $ 22,852.32 |
The key with a VAR variable that you do not want counted (or summed or what have you) is to set it to missing:
data yy; Input empid $5. @6 tier_level $6. @12 current_month_pmt Last_month Future_month; Infile cards; array pmt current_month_pmt Last_month Future_month; do i = 1 to dim(pmt); if pmt[i]=0 then pmt[i]=.; end; drop i; cards; 18457 Tier1 215.35 408.13 410.23 12877 Tier1 715.35 1178.83 1120.95 17894 Tier1 115.35 678.57 0 86886 Tier2 0 6788.13 6810.23 54545 Tier2 115.08 278.78 320.11 21478 Tier2 895.04 0 785.57 25710 Tier2 235.45 2575.29 2879.23 25791 Tier3 98.25 550.83 623.87 99987 Tier3 0 8908.20 9902.13 ; Run; Proc tabulate data=yy S=[foreground=black just=c]; class tier_level; var current_month_pmt Last_month Future_month; table tier_level = '' all={label='Total' s=[just=C]}, (current_month_pmt='Current Month' Last_month='Last Month' Future_month='Next Month') * (n='# employees'*F=12. sum='$ Amount' * f=dollar18.2) /misstext='0'; run;
The key with a VAR variable that you do not want counted (or summed or what have you) is to set it to missing:
data yy; Input empid $5. @6 tier_level $6. @12 current_month_pmt Last_month Future_month; Infile cards; array pmt current_month_pmt Last_month Future_month; do i = 1 to dim(pmt); if pmt[i]=0 then pmt[i]=.; end; drop i; cards; 18457 Tier1 215.35 408.13 410.23 12877 Tier1 715.35 1178.83 1120.95 17894 Tier1 115.35 678.57 0 86886 Tier2 0 6788.13 6810.23 54545 Tier2 115.08 278.78 320.11 21478 Tier2 895.04 0 785.57 25710 Tier2 235.45 2575.29 2879.23 25791 Tier3 98.25 550.83 623.87 99987 Tier3 0 8908.20 9902.13 ; Run; Proc tabulate data=yy S=[foreground=black just=c]; class tier_level; var current_month_pmt Last_month Future_month; table tier_level = '' all={label='Total' s=[just=C]}, (current_month_pmt='Current Month' Last_month='Last Month' Future_month='Next Month') * (n='# employees'*F=12. sum='$ Amount' * f=dollar18.2) /misstext='0'; run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.