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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.