BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mrafael03
Obsidian | Level 7

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
Super User Tom
Super User

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
Obsidian | Level 7

Please see attached expected output. Hopefully this is helpful

BrunoMueller
SAS Super FREQ

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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 18 replies
  • 1930 views
  • 3 likes
  • 4 in conversation