data test1;
infile datalines;
input setup_date date9. Division $ cnt;
return;
datalines;
1Jun2018 Central 4
1May2018 Central 5
1Apr2019 Atlantic 3
1Feb2019 Atlantic 11
1Jan2017 Pacific 11
1Mar2019 Central 11
;run;
data test2;
set test1;
format setup_date date9.;
run;
/*mydata is the data i start with (queried from the database)
I am just showing test1 and 2 because this is a created example*/
data mydata;
set test2;
year1 = year(setup_date);/*returns numeric*/
year2 = put(year1,4.);/*change to character*/
Month1 = put(setup_date,monname3.);/*change to character*/
run;
proc report data = mydata nowd style(summary)=Header;
column year2 Month1 cnt rtot;
define year2 /group ;
define Month1 /group ;
define cnt /sum f=comma6.;
define RTot /computed f=comma16. "Row Totals";
compute RTot;
RTot=sum(cnt.sum);/*row_tot*/
endcomp;
compute before;
sum=cnt.sum;
endcomp;
compute after;
year2='Grand Total';
endcomp;
break after year2 / ol summarize skip;/*total by group*/
rbreak after /summarize;
run;
/*if you run the code the year2 is in correct order however the month is in alpha order and not by month
How can I fix this?*/
Hi:
The character variable for month is sorting as I would expect. Consider these changes to your code:
Which produces the correct order for MONTH1:
but only because the MONTHNUM variable is added to the report as a numeric variable.
Hope this helps,
Cynthia
Hi:
The character variable for month is sorting as I would expect. Consider these changes to your code:
Which produces the correct order for MONTH1:
but only because the MONTHNUM variable is added to the report as a numeric variable.
Hope this helps,
Cynthia
Don't even need a month number variable the original SETUP_date works just fine with the proper format and order option.
proc report data = mydata nowd style(summary)=Header; column year2 setup_date cnt rtot; define year2 /group ; define setup_date /group f=monname3. order=internal ; define cnt /sum f=comma6.; define RTot /computed f=comma16. "Row Totals"; compute RTot; RTot=sum(cnt.sum);/*row_tot*/ endcomp; compute before; sum=cnt.sum; endcomp; compute after; year2='Grand Total'; endcomp; break after year2 / ol summarize skip;/*total by group*/ rbreak after /summarize; run;
@Cynthia_sas wrote:
Clever approach! I figured the original poster had some other need for character variables. The way they were calculating totals was a bit odd too and not the say I would have done that part of the report either.
Cynthia
I didn't bother to look at the body for the other columns, just enough to see that my values matched yours.
I just wanted to emphasize the date + format is a powerful grouping tool.
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 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: