BookmarkSubscribeRSS Feed
braam
Quartz | Level 8

Is there a general way to display row (line) number in a table generated by PROC REPORT?

For example, I would like to know a simple, general code to add line number to both tables.

 

 


proc report data= sashelp.cars;
	where Make in ("Acura", "Audi");
	column Make Type MSRP;
	define Make / group;
	define Type / group;
	define MSRP / analysis mean; run;
	

proc report data= sashelp.cars;
	where Make in ("Acura", "Audi");
	column Make Type Origin MSRP;
	define Make / group;
	define Origin / group;
	define MSRP / analysis mean; run;

 

 


Example 1

Make Type MSRP
Acura SUV $36,945
  Sedan $34,772
  Sports $89,765
Audi Sedan $41,108
  Sports $49,630
  Wagon $44,965

 


Example 2

Make Type Origin MSRP
Acura SUV Asia $36,945
  Sedan   $23,820
  Sedan   $26,990
  Sedan   $33,195
  Sedan   $43,755
  Sedan   $46,100
  Sports   $89,765
Audi Sedan Europe $25,940
  Sedan   $35,940
  Sedan   $31,840
  Sedan   $33,430
  Sedan   $34,480
  Sedan   $36,640
  Sedan   $39,640
  Sedan   $42,490
  Sedan   $44,240
  Sedan   $42,840
  Sedan   $49,690
  Sedan   $69,190
  Sedan   $48,040
  Sports   $84,600
  Sports   $35,940
  Sports   $37,390
  Sports   $40,590
  Wagon   $40,840
  Wagon   $49,090

 

7 REPLIES 7
Cynthia_sas
SAS Super FREQ

Hi:
PROC REPORT does not support an OBS number column like PROC PRINT. If you try to create a "fake" line number, numbers may not be as you expect, as you discovered in one of your other examples.

Here's another example of how PROC REPORT line numbers can get "off".

line_numbers_not_desired.png

Cynthia

mkeintz
PROC Star

As @Cynthia_sas says, there is no way to tell report to create a line number.  But you COULD

  1. tell proc report to output to a data set  (data set myrptdata below)
  2. Add a line number variable to that data set  (data set view need)
  3. Rerun a slightly revised proc report against the newly enhanced report data
ods output report=myrptdata;
proc report data= sashelp.cars;
	where Make in ("Acura", "Audi");
	column Make Type MSRP;
	define Make / group;
	define Type / group;
	define MSRP / analysis mean;
run;
ods output close;

data need/view=need;
  set myrptdata;
  line=_n_;
run;
proc report data= need;
	where Make in ("Acura", "Audi");
	column line Make Type MSRP;
	define Make / group;
	define Type / group;
	define MSRP / analysis mean;
run;

Not a very appealing solution, I admit, and I imagine it would not be robust under all conditions, but it works with your reports.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Cynthia_sas
SAS Super FREQ
Hi:
But now you're making 3 passes through the data. My example (without GROUP usage) in the screen shot only does 2 passes through the data. If you are using GROUP usage though, your solution is probably the best alternate approach.
Cynthia
mkeintz
PROC Star

 

I agree that a data set view is a pass through the data, but it does avoid the disk activity of creating a data set file, merely to read it back in the third pass.  And the extra pass is being applied to the proc report output, likely a much smaller dataset than the original (both in columns and rows).  So it may not be too far from two passes.

 

 

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Cynthia_sas
SAS Super FREQ

Hi:

  Yes, that's right. Especially if you need to get numbers added to grouped data, that forces you to summarize, whether with TABULATE, MEANS, REPORT, DATA step or SQL. So your approach is probably the least amount of passes for grouping the data.

 

Cynthia

mkeintz
PROC Star

Well I guess it can be done in proc report in one pass.  Roger DeAngelis shows how over in GitHub:

 

https://github.com/rogerjdeangelis/utl-is-proc-report-more-powerfull-than-proc-summary-or-proc-means 

 

The COMPUTE blocks need proper attention to the TYPE variable (the most minor of the group variables).  It is blank in the aggregate row - providing a value to test for in order to correct the automatic increment of the line number.  Of course, that means you better not have a blank TYPE in the original data set.

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Cynthia_sas
SAS Super FREQ
That is the spanner in the works!
Cynthia

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 2450 views
  • 0 likes
  • 3 in conversation