BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
supp
Pyrite | Level 9

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 

  1. 'year'
  2. year value
  3. 'month'
  4. month value
  5. '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. 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

image.png 

View solution in original post

2 REPLIES 2
Tom
Super User Tom
Super User

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

image.png 

supp
Pyrite | Level 9

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

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 414 views
  • 1 like
  • 2 in conversation