Thanks for the sample code @BrunoMueller really appreciate it.
I have these codes on my original script. What I'm trying to achieve is on how I will include the 12 month period "YEARMONTH" on my final report without hard coding it as shown above
/*aggregate data, since some yrmth appear more tan once*/
proc sql;
create table dataset_aggr as select
id
,yrmth
,sum(count) as count
from dataset
group by id,yrmth;
quit;
/*transpose the data*/
proc transpose data=dataset_aggr out=ds_trsp(drop=_name_);
by id;
id yrmth;
var count;
run;
Either create the total as a separate row in the TALL dataset.
proc sql;
create table dataset_aggr as select
id
,cats(yrmth) as name length=6
,sum(count) as count
from dataset
group by id,yrmth
union
select
id
,'TOTAL' as name length=6
,sum(count) as count
from dataset
group by id
;
quit;
...
id name;
...
Or create the total in the WIDE dataset.
The second will be easy if ID is character.
data want;
set ds_trsp;
total=sum(of _numeric);
run;
Or make your transposed dataset have rational and uniquely prefixed valid SAS names by using the PREFIX= option on PROC TRANSPOSE so you can use a prefixed variable list in the SUM() function call.
proc transpose prefix=YYMM_ ....
...
total=sum(of YYMM_:);
...
Please see attached expected output. Hopefully this is helpful
Your Proc REPORT is very close to what you want, just some minor changes:
You can send the output directly to Excel using ODS EXCEL, as it looks like you want to have it in Excel.
proc report data=two;
columns id name age count, yrmth count=total;
define id / group;
define name / group;
define age / group;
define yrmth / across " ";
define count / sum " ";
define total / sum "Total";
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.