Can I show row (line) number in PROC REPORT?

[braam]

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

[Cynthia_sas]

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".

Cynthia

[mkeintz]

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.

[Cynthia_sas]

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]

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.

[Cynthia_sas]

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]

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.

[Cynthia_sas]

That is the spanner in the works!
Cynthia