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 mydata;
length year2 $12.;
set test2;
year1 = year(setup_date);/*returns numeric*/
monthsum = month(setup_date);/*returns numeric*/
year2 = put(year1,4.);/*change to character*/
Month1 = put(setup_date,monname3.);/*change to character*/
format setup_date date9.;
run;
proc sort data=mydata;by year1 descending cnt;run;
Produces this output
year2 | setup_date | Division | cnt | year1 | monthsum | Month1 |
2017 | 01Jan2017 | Pacific | 11 | 2017 | 1 | Jan |
2018 | 01May2018 | Central | 5 | 2018 | 5 | May |
2018 | 01Jun2018 | Central | 4 | 2018 | 6 | Jun |
2019 | 01Feb2019 | Atlantic | 11 | 2019 | 2 | Feb |
2019 | 01Mar2019 | Central | 11 | 2019 | 3 | Mar |
2019 | 01Apr2019 | Atlantic | 3 | 2019 | 4 | Apr |
proc report data = mydata nowd style(summary)=Header;
column year2 monthsum Month1 cnt rtot;
define year2 /group ;
define monthsum /group noprint order = data; /*will sort the month in month order*/
define Month1 /across order = data ;
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;
Produces this proc report
Month1 | ||||||||
year2 | Jan | May | Jun | Feb | Mar | Apr | cnt | Row Totals |
2017 | 1 | . | . | . | . | . | 11 | 11 |
2017 | 1 | . | . | . | . | . | 11 | 11 |
2018 | . | 1 | . | . | . | . | 5 | 5 |
. | . | 1 | . | . | . | 4 | 4 | |
2018 | . | 1 | 1 | . | . | . | 9 | 9 |
2019 | . | . | . | 1 | . | . | 11 | 11 |
. | . | . | . | 1 | . | 11 | 11 | |
. | . | . | . | . | 1 | 3 | 3 | |
2019 | . | . | . | 1 | 1 | 1 | 25 | 25 |
Grand Total | 1 | 1 | 1 | 1 | 1 | 1 | 45 | 45 |
desired output is to move the year from row the column and segment the month by the individual year
Desired outout | ||||||||
2017 | 2018 | 2019 | CNT | |||||
Jan | May | Jun | Feb | Mar | Apr | cnt | Row Totals | |
1 | . | . | . | . | . | 11 | 11 | |
1 | . | . | . | . | . | 11 | 11 | |
. | 1 | . | . | . | . | 5 | 5 | |
. | . | 1 | . | . | . | 4 | 4 | |
. | 1 | 1 | . | . | . | 9 | 9 | |
. | . | . | 1 | . | . | 11 | 11 | |
. | . | . | . | 1 | . | 11 | 11 | |
. | . | . | . | . | 1 | 3 | 3 | |
. | . | . | 1 | 1 | 1 | 25 | 25 | |
1 | 1 | 1 | 1 | 1 | 1 | 45 | 45 |
You don't need the second step where you compute bits and pieces of the setup_date. Instead, replicate the setup_date into new variables and use the new variables in the Proc REPORT with an appropriate format.
Because you want the setup_date to have a role in both rows and columns (as across) you will need a replicate for each role.
Example:
The important features of Proc REPORT are NOCOMPLETECOLS and the column stacking syntax (comma).
I added a third data value "1Sep2018 Central 13" that is a later month with a higher cnt, and I presume it's row should appear before earlier but lower count months.
ods html file='report.html';
data have;
infile datalines;
input setup_date date9. Division $ cnt;
return;
datalines;
1Jun2018 Central 4
1May2018 Central 5
1Sep2018 Central 13
1Apr2019 Atlantic 3
1Feb2019 Atlantic 11
1Jan2017 Pacific 11
1Mar2019 Central 11
;run;
data for_report;
set have;
year_col = setup_date;
month_col = setup_date;
year_row = setup_date;
month_row = setup_date;
format setup_date year_col -- month_row date9.;
run;
proc sort data=for_report;
by setup_date;
run;
proc report data=for_report nocenter NOCOMPLETECOLS style(summary)=Header;
column
year_row
month_row
year_col,month_col
cnt
cnt=row_total;
define year_row / noprint format=year4. 'year' order order=data ;*noprint;
define month_row / noprint format=monname3. 'month'; *noprint;
define year_col / ' ' format=year4. across order=data;
define month_col / ' ' format=monname3. across order=data;
define cnt / sum order descending;
define row_total / 'Row/Totals';
break after year_row / summarize;
rbreak after / summarize;
run;
ods html close;
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.