Arranging variables in proc report

Q1983 — Mon, 13 Apr 2020 00:00:14 GMT

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

Re: Arranging variables in proc report

RichardDeVen — Mon, 13 Apr 2020 03:04:50 GMT

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;

topic Arranging variables in proc report in SAS Programming

Arranging variables in proc report

Re: Arranging variables in proc report