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;
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
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".
As @Cynthia_sas says, there is no way to tell report to create a line number. But you COULD
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.
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.
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.
Well I guess it can be done in proc report in one pass. Roger DeAngelis shows how over in GitHub:
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.
SAS is headed back 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.
Interested in speaking? Content from our attendees is one of the reasons that makes SAS Innovate such a special event!
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.