Need help withTabular Reporting

[vicky07]

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

[ballardw · Accepted solution]

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;

[vicky07]

Thank you very much!