BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vicky07
Quartz | Level 8

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
1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

2 REPLIES 2
ballardw
Super User

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
Quartz | Level 8
Thank you very much!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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