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
... View more