How to arrange dates using proc transpose and adding the total?

[mrafael03]

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;

[Tom]

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_:);
...

[mrafael03]

Please see attached expected output. Hopefully this is helpful

[BrunoMueller · Accepted solution]

Your Proc REPORT is very close to what you want, just some minor changes:

• In the COLUMN statement you can either use acrossVar, analysisVar or analysisVar, acrossVar, see below
• add an empty column label, this will suppress the printing
• adding the statement options missing="0"; before Proc REPORT will print a 0 instead of ., please set it back to . after Proc REPORT

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;