topic Re: Sum Column and Collapse Multiple Rows in SAS Programming

Sum Column and Collapse Multiple Rows

agille05 — Tue, 06 Dec 2022 01:24:03 GMT

Hi all,

I would think this would be something straightforward, but this is plaguing me (so really appreciate any suggestions).

I have a dataset that looks like the following:

Day	_0	_1	_N
Monday	45	56	12
Tuesday	14	5	8
Wednesday	45	45	92
Thursday	45	48	78
Friday	47	52	7
Saturday	69	15	5
Sunday	3	20	2

What I'd like to do is collapse the weekdays (Monday to Friday) and weekends (Saturday and Sunday) while also summing the rows. So it would look like this:

Day	_0	_1	_N
Weekday	196	206	197
Weekend	72	35	7

Any suggestions? And thank you!

Re: Sum Column and Collapse Multiple Rows

ballardw — Tue, 06 Dec 2022 03:38:35 GMT

One of the common approaches to mapping values to groups is a custom format, though perhaps a tad of overkill for 7 rows of data.

Proc format;
value $daytype
'Monday', 'Tuesday','Wednesday','Thursday','Friday'='Weekday'
'Saturday','Sunday' = 'Weekend'
;
run;

/* dataset*/
proc summary data=have nway;
   class day;
   format day $daytype.;
   var _0 _1 _n;
   output out=want (drop=_type_ _freq_) sum=;
run;

/*report*/
proc report data=have;
   columns day _0 _1 _n;
   define day / group format=$daytype.;
run;

Formats are a very powerful tool in SAS as the groups created will be honored by reporting procedures, analysis and generally in graphing.

Note, the way to indicate data as a best practice is to include it as data step code such as:

data have;
  input Day:$10. 	_0 	_1 	_N;
datalines;
Monday  	45 	56 	12
Tuesday 	14 	5 	8
Wednesday 	45 	45 	92
Thursday 	45 	48 	78
Friday 	47 	52 	7
Saturday 	69 	15 	5
Sunday 	3 	20 	2
;

That way we have a data set to test with and don't have to guess as to variable type.

Re: Sum Column and Collapse Multiple Rows

andreas_lds — Tue, 06 Dec 2022 06:34:02 GMT

Proc tabulate can do this, using the format provided by @ballardw:

proc tabulate data=work.have;
   class Day;
   var _0 _1 _N;
   
   format Day $DayType.;
   
   table Day= ' ', _0 _1 _N / box= 'Day';
   keylabel sum = ' ';
run;