topic Re: creating summary table in SAS in New SAS User

creating summary table in SAS

sasprogramming — Tue, 10 Dec 2019 05:26:49 GMT

I have the following dataset which looks like this:

ID	Month	Points
1	Jan	100
2	Jan	100
3	Jan	200
4	Feb	100
5	Feb	200
6	Feb	300
7	Mar	100
8	Mar	200
9	Apr	300
10	Apr	100
11	Apr	200
12	Apr	300

Is there a way in SAS to produce a summary table that looks like this?

	100	200	300	Total
Jan	67%	33%	0%	3
Feb	33%	33%	33%	3
Mar	50%	50%	0%	2
Apr	25%	25%	50%	4
Total	5	4	3	12

Thank you.

Re: creating summary table in SAS

Sathish_jammy — Tue, 10 Dec 2019 09:13:17 GMT

Proc Tabulate will help you to summary the % values

proc tabulate data = have;
class month points;
table month all, points*(n*f=8. colpctn='%') all='Total'*(n*f=8.)/rts=10;
run;

Re: creating summary table in SAS

ed_sas_member — Tue, 10 Dec 2019 11:21:35 GMT

Hi @sasprogramming

An easy way to do that is to use a proc freq.

proc freq data=have2;
	table month*points / nocol nopercent nofreq ;
run;

If you want to display months in a logical order, you can add a format:

proc format;
	value monthn
		1 = 'Jan'
		2 = 'Feb'
		3 = 'Mar'
		4 = 'Apr'
		5 = 'May'
		6 = 'Jun'
		7 = 'Jul'
		8 = 'Aug'
		9 = 'Sep'
		10 = 'Oct'
		11 = 'Nov'
		12 = 'Dec';
run;
	
data have2;
set have;
attrib monthn format=monthn.;
select (month);
   when ('Jan') monthn=1;
   when ('Feb') monthn=2;
   when ('Mar') monthn=3;
   when ('Apr') monthn=4;
   when ('May') monthn=5;
   when ('Jun') monthn=6;
   when ('Jul') monthn=7;
   when ('Aug') monthn=8;
   when ('Sep') monthn=9;
   when ('Oct') monthn=10;
   when ('Nov') monthn=11;
   when ('Dec') monthn=12;
   otherwise;
end;
drop month;
rename monthn = Month;
run;

proc freq data=have2;
	table month*points / nocol nopercent nofreq ;
run;

Re: creating summary table in SAS

ed_sas_member — Tue, 10 Dec 2019 11:51:01 GMT

Hi @sasprogramming

Another way to do this is to use PROC REPORT.

The final report will be closer to what you expect.

However, the "structure" is less flexible -> in the following code, only 3 modalities for the variable POINTS are taken into account (100, 200, 300) as the code refers directly to the column number in the report. Should you have more modalities, you will need to adjust the code.

Code:

data have;
infile datalines dlm="09"x;
input ID	Month $	Points;
cards;
1	Jan	100
2	Jan	100
3	Jan	200
4	Feb	100
5	Feb	200
6	Feb	300
7	Mar	100
8	Mar	200
9	Apr	300
10	Apr	100
11	Apr	200
12	Apr	300
;
run;

data want;
set have;
select (month);
   when ('Jan') monthn=1;
   when ('Feb') monthn=2;
   when ('Mar') monthn=3;
   when ('Apr') monthn=4;
   when ('May') monthn=5;
   when ('Jun') monthn=6;
   when ('Jul') monthn=7;
   when ('Aug') monthn=8;
   when ('Sep') monthn=9;
   when ('Oct') monthn=10;
   when ('Nov') monthn=11;
   when ('Dec') monthn=12;
   otherwise;
end;
run;

proc report data=want;

	column monthn month dum_month points, (n pct dum_pct) total;
	
	define monthn / group id order=data noprint;
	define month / group noprint;
	define dum_month / computed "Month";
	define points / across;
	define n / "n" noprint ;
	define pct / computed "%" f=percent8.0 noprint;
	define dum_pct / "%" computed;
	define total / computed 'Total';
	
	/* Sum totals in row for further calculation */
	compute before month;
 	    _total = sum(_c4_, _c7_, _c10_);
    endcomp;
    
    /* Compute percentages from frequencies (n) and totals in row (_total) */
    compute pct;
		if _c4_ > 0 then _c5_ = _c4_ / _total;
		else _c5_ = 0;
		if _c7_ > 0 then _c8_ = _c7_ / _total;
		else _c8_ = 0;
		if _c10_> 0 then _c11_ = _c10_ / _total;
		else _c11_= 0; 
	endcomp;

	/* Sum totals in row */
	compute total;
	     total = sum(_c4_, _c7_, _c10_);
	endcomp;
 
 	/* Display either month or "Total" according to the type of row (last row or not)*/
	compute dum_month / character length=5;
		if _BREAK_="_RBREAK_" then dum_month = 'Total';
		else dum_month = month;
	endcomp;
	
	/* Display either pct or freq according to the type of row (last row or not)*/
	compute dum_pct / char;
		if _BREAK_="_RBREAK_" then do;
			_c6_ = put(_c4_,best8.);
			_c9_ = put(_c7_,best8.);
			_c12_ = put(_c10_,best8.);
			end;
		else do;
			_c6_ = put(_c5_,percent8.0);
			_c9_ = put(_c8_,percent8.0);
			_c12_ = put(_c11_,percent8.0);
			end;
		
	endcomp;
	
	rbreak after / summarize ;

run;