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
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.
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
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
Thanks @Tom , this did exactly what I was looking for!
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: