BookmarkSubscribeRSS Feed
agille05
Fluorite | Level 6

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!

2 REPLIES 2
ballardw
Super User

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.

 

andreas_lds
Jade | Level 19

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;
 

 

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 608 views
  • 0 likes
  • 3 in conversation