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;