topic Re: Can I show row (line) number in PROC REPORT? in SAS Procedures

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

braam — Sun, 24 Nov 2019 20:50:07 GMT

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

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

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

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

Cynthia_sas — Sun, 24 Nov 2019 21:47:02 GMT

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

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

mkeintz — Sun, 24 Nov 2019 22:06:59 GMT

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

tell proc report to output to a data set (data set myrptdata below)
Add a line number variable to that data set (data set view need)
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.

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

Cynthia_sas — Mon, 25 Nov 2019 02:58:36 GMT

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

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

mkeintz — Mon, 25 Nov 2019 04:14:40 GMT

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.

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

Cynthia_sas — Mon, 25 Nov 2019 18:13:57 GMT

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

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

mkeintz — Wed, 27 Nov 2019 04:12:34 GMT

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.

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

Cynthia_sas — Wed, 27 Nov 2019 14:35:04 GMT

That is the spanner in the works!
Cynthia