topic Re: Cleaning up output from proc report in SAS Procedures

Cleaning up output from proc report

supp — Tue, 21 Jan 2020 16:22:17 GMT

Hello, I would like to cleanup a report I am creating using proc report. I have data similar to:

data have;	
	infile datalines dlm=',';
		input order group :$20. year month value;
datalines;
1,group 1,2019,7,10
1,group 1,2019,8,10
1,group 1,2019,9,15
1,group 1,2019,10,100
1,group 1,2019,11,100
1,group 1,2019,12,500
1,group 1,2020,1,1000
2,group 2,2019,7,10
2,group 2,2019,8,10
2,group 2,2019,9,10
2,group 2,2019,10,12
2,group 2,2019,11,100
2,group 2,2019,12,250
2,group 2,2020,1,300
3,group 3,2019,9,5
3,group 3,2019,10,5
3,group 3,2019,11,5
3,group 3,2019,12,7
3,group 3,2020,1,100
4,group 3 / group 2,2019,9,0.50
4,group 3 / group 2,2019,10,0.42
4,group 3 / group 2,2019,11,0.05
4,group 3 / group 2,2019,12,0.03
4,group 3 / group 2,2020,1,0.3
;
run;

I want to report by group (in order of the order variable) with month and year going across. This is what I have so far:

proc report data = have nowd;
	columns order group year,month,value;

	define order 	/ noprint;
	define group	/ group ;
	define year	/ across ;
	define month	/ across ;
	define value	/ analysis ;
run;

In my output I have five header rows

'year'
year value
'month'
month value
'value

Is it possible to supress the rows that say 'year','month' and 'value (1,3 and 5)?

Also, the report is producing columns that I don't have in my data. For example the year month combination of year 2020 and month 7. Is it possible to suppress these columns that I don't have data for?

I included an image to further explain.

Ultimately I want to put this in excel.

Re: Cleaning up output from proc report

Tom — Tue, 21 Jan 2020 16:44:02 GMT

Set the column headers in the DEFINE statements to blank for the labels you don't want.

Change the order of the across and value variables in the eliminate the extra blank line. It seems illogical to list the analysis variable before the across variables, but it does eliminate that extra blank line.

Add the NOZERO option tot he define statement for VALUE.

proc report data = have nowd ;
	columns order group value,year,month;
	define order 	/ noprint;
	define group	/ group ;
	define year	/ across ' ';
	define month	/ across ' ';
	define value	/ analysis ' ' nozero;
run;

                                                     2019                                     2020

  group                         7          8          9         10         11         12          1
  group 1                      10         10         15        100        100        500       1000
  group 2                      10         10         10         12        100        250        300
  group 3                       .          .          5          5          5          7        100
  group 3 / group 2             .          .        0.5       0.42       0.05       0.03        0.3

Re: Cleaning up output from proc report

supp — Tue, 21 Jan 2020 16:52:36 GMT

Thanks @Tom , this did exactly what I was looking for!