Need help withTabular Reporting

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 82
Accepted Solution

Need help withTabular Reporting

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

Accepted Solutions
Solution
‎05-15-2018 10:50 AM
Super User
Posts: 13,517

Re: Need help withTabular Reporting

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;

View solution in original post


All Replies
Solution
‎05-15-2018 10:50 AM
Super User
Posts: 13,517

Re: Need help withTabular Reporting

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;
Frequent Contributor
Posts: 82

Re: Need help withTabular Reporting

Thank you very much!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 116 views
  • 0 likes
  • 2 in conversation