data have;
infile datalines;
input setup_date date9. Type $ cnt;
return;
datalines;
1jun2019 Central 1
1jun2019 West 1
12jul2019 East 1
14aug2019 West 1
15aug2019 West 1
20aug2019 East 1
;run;
data have2;
set have;
monthsum = month(setup_date);/*returns numeric*/
Month1 = put(setup_date,monyy5.);/*change to character*/
format setup_date date9.;
format setup_date date9.;
run;
proc report data=have2 wrap style(column)={just=center};
column Type monthsum MONTH1 cnt ;
define Type / group style (column)=[cellwidth=100pt] "EXC TYPE";
define monthsum /group noprint order = internal;
define MONTH1 /across style(column)=[cellwidth=100pt] "MONTH";
define cnt / sum "Row Total" style(column)=[cellwidth=90pt];
rbreak after / summarize style (summary)= Header;
compute after;
Type = 'Grand Total';
endcomp;
run;
Issue#1 - The proc report appears not to roll up totals correctly. I get extra rows in the output
Issue#2 - The Month1 (across) is not sorting by month despite using the monthsum.
Try replacing both monthsum and month1 with setup_date defined as an across column with order=internal and format monyy5:
define setup_date / across order = internal format = monyy5.;
What do you want out. Might be easiest to just make a date value that is the first of the month using the INTNX() function to use for your ACROSS variable.
data have;
input setup_date :date9. Type :$11. cnt;
format setup_date date9.;
datalines;
1jun2019 Central 1
1jun2019 West 1
12jul2019 East 1
14aug2019 West 1
15aug2019 West 1
20aug2019 East 1
;
proc print data=have; title 'HAVE'; run;
data have2;
set have;
Month2 = intnx('month',setup_date,0,'b');
format month2 monyy7. ;
run;
proc print data=have2; title 'HAVE2'; run;
proc report data=have2 wrap style(column)={just=center};
title 'PROC REPORT';
column Type cnt,month2 ;
define Type / group style (column)=[cellwidth=100pt] "EXC TYPE";
define MONTH2 /across order=internal style(column)=[cellwidth=100pt] "MONTH";
define cnt / sum ' ' style(column)=[cellwidth=90pt];
rbreak after / summarize style (summary)= Header;
compute after;
Type = 'Grand Total';
endcomp;
run;
title;
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 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.