BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
mreynaud
Obsidian | Level 7

Hello everyone,

 

I have a simple summary table that shows the number of assignments per quarter. It looks something like this:

TypeOCT23NOV23DEC23
AdHoc1098
Report573
Survey1  
Modification 354

 

I want to develop a "Total" column that gives the sum of each assignment type for the entire quarter, so something like this:

   TypeOCT23NOV23DEC23Total
AdHoc109827
Report57315
Survey1  1
Modification 35412

 

A note, the three month columns (OCT23, NOV23, DEC23) will change each quarter when this report is ran. So it would be nice to have a solution that accounts for this. I was reviewing this post: https://communities.sas.com/t5/SAS-Procedures/How-to-sum-selected-array-variables/td-p/129788 

Their dataset is structured differently than mine, would I also need to restructure it similarly?

 

Here are some datalines that output a summary table of what I have so far.

data have;
length Type $12;
input Type OCT23 NOV23 DEC23;
datalines;
AdHoc 10 9 8
Report 5 7 3
Survey 1 0 0
Modification 3 5 4
;
run;

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star
A simple addition to your DATA step would do the trick:

total = sum(of _numeric_);

Note, however, that it would be much easier to work with this data in a different form with 3 variables: type, date, and amount.

View solution in original post

6 REPLIES 6
Astounding
PROC Star
A simple addition to your DATA step would do the trick:

total = sum(of _numeric_);

Note, however, that it would be much easier to work with this data in a different form with 3 variables: type, date, and amount.
mreynaud
Obsidian | Level 7
This worked perfectly! I'm so glad it was such a simple solution
PaigeMiller
Diamond | Level 26

The answer marked correct clearly works on your example data set, @mreynaud , but there are conditions that have to hold for this solution to work on your real data, which you need to be aware of.

 

If your real world data set has numeric values that are not to be included in the sum (such as a numeric ID value, or a loan balance, or a crop yield, or any other variable that is numeric), then this solution does not work. The solution presented using a long data set does not have this drawback, and as stated has many other advantages as well.

--
Paige Miller
Astounding
PROC Star

@PaigeMiller ,

 

While you are absolutely correct about this, I proposed a solution based on the question as presented.  Actually, I fully expected that the original poster would write back saying that the solution didn't work (because of the presence of other numeric variables).  The lesson (in my mind) was going to be that you need to accurately describe the data if you want an accurate solution.  I thought recommendations to restructure the data and then learn a summary procedure were too complex for a programmer asking the original question.

 

If another variable is present, the fix might be any one of a number of things that use the current structure to the data.  For example, if the data added a numeric variable YEAR, it might be:

 

total = sum(of _numeric_) - year;

 

Or even (if there are 100 numeric variables (beginning with YEAR) added after the "month" variables:

 

total = sum(of type - numeric - year) - year;

 

SAS won't care that TYPE is character, it would simply omit TYPE from the specified list.

 

And if the other numeric variables are scattered throughout the data as possibility would be:

 

total = sum(of jan2:, of feb2:, of mar2:, of apr2:, of may2:, of jun2:, of jul2:, of aug2:, of sep2:, of oct2:, of nov2:, of dec2:);

 

SAS should complain if not all of these variables exist, but it should compute properly anyway.  Yes, this solution only lasts through 2029, but by that time all involved should be wiser and able to devise something better.

 

For the sophisticated, you could even try:

 

data want;

   set have (keep= jan2: feb2: mar2: apr2: may2: jun2: jul2: aug2: sep2: oct2: nov2: dec2:);

   type = sum(of _numeric_);

   set have;

run;

 

My bad for not mentioning that the inclusion of other numeric variables would throw off the total.  I did fully expect that the issue would arise during the process of ironing out a solution.

PaigeMiller
Diamond | Level 26

Typically, this wide layout of your data is a poor layout. Avoid having calendar information in a variable name. A long layout of data, where the calendar information is a value of a variable named MONTH. Most analyses in SAS PROCs require the long layout rather than the wide layout.

 

In this case, it probably doesn't matter which layout you use, wide or long, but in many other cases, wide is a poor choice, and long will make your programming easier; and so using a long layout is a good habit to get into.

 

Solution for wide data: 

data want;
    set have;
    total = sum(oct23,nov23,dec23);
run;

 

Pretty simple, except you have to type all three month names. And if the time period changes, you have to re-type the month names.

 

 

LONG data set.

 

data work.LONG;
  infile datalines dsd truncover;
  input Type:$12. month:MONYY7. value:32.;
  format month MONYY7.;
datalines4;
AdHoc,OCT2023,10
AdHoc,NOV2023,9
AdHoc,DEC2023,8
Report,OCT2023,5
Report,NOV2023,7
Report,DEC2023,3
Survey,OCT2023,1
Survey,NOV2023,0
Survey,DEC2023,0
Modification,OCT2023,3
Modification,NOV2023,5
Modification,DEC2023,4
;;;;

 

Solution for long data set

 

proc summary data=long nway;
    var value;
    class month;
    output out=want sum=total;
run;


Also, pretty simple, but note that if you use the long data set, your code doesn't have to change next month to handle different months. So in the long run, the long data set will take less time for you to program

--
Paige Miller
ballardw
Super User

A very powerful tool in SAS is the ability to create report groups based on the Formatted values of a variable. To extend @PaigeMiller's example to a longer data set and see some options using the different formats for the Month variable:

data work.LONG2;
  infile datalines dsd truncover;
  input Type:$12. month:MONYY7. value:32.;
  format month MONYY7.;
datalines4;
AdHoc,OCT2023,10
AdHoc,NOV2023,9
AdHoc,DEC2023,8
Report,OCT2023,5
Report,NOV2023,7
Report,DEC2023,3
Survey,OCT2023,1
Survey,NOV2023,0
Survey,DEC2023,0
Modification,OCT2023,3
Modification,NOV2023,5
Modification,DEC2023,4
AdHoc,SEP2023,18
AdHoc,AUG2023,19
AdHoc,JUL2023,11
Report,SEP2023,1
Report,AUG2023,4
Report,JUL2023,8
Survey,SEP2023,3
Survey,AUG2023,2
Survey,JUL2023,1
Modification,SEP2023,10
Modification,AUG2023,13
Modification,JUL2023,12
;;;;

proc tabulate data=work.long2;
   class type month;
   var value;
   table type,
         (Month='' All='All months')*value=''*sum=''
         ;
run;

proc tabulate data=work.long2;
   class type month;
   format month yyq.;
   var value;
   table type,
         (Month='' All='All Quarters')*value=''*sum=''
         ;
run;

See the difference just changing the format makes? You can get quite interesting results with actual dates such as weekly, monthly, quarterly or annual summaries if you have actual dates of record just by changing the format. With formats like Month and Qtr you can even combine values across years if that is desirable. Just by changing the format.

Plus with some additional work you can create custom formats to indicate values based on a fiscal year or even different intervals such a perhaps reporting the recent values weekly and then for older values use month, quarter or yearly, sort of a "year to date" difference. All without changing a single value in the actual data set.

The groups created by formats are also used in analysis procedures and generally for graphing (some restrictions on custom date/time/datetime formats may apply).

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
  • 6 replies
  • 692 views
  • 1 like
  • 4 in conversation