Help using Base SAS procedures

Proc Report

Posts: 0

Proc Report


I wish to achieve the result below ie to count ID by Group (1) using proc report. Please note that APMP Total is expanded to Sub-Group column. Alternatively, the format for report (2).

Group | Sub-Group | ID
APMP | A1 | 22121123
APMP | B2 | 22111121
APMP | C2 | 22111111
APMP | C2 | 20112012
APMP subtotal | 4

Group | Sub-Group | ID | Count
APMP | A1 | 22121123 |
APMP | B2 | 22111121 |
APMP | C2 | 22111111 |
APMP | C2 | 20112012 | 4
Posts: 0

Re: Proc Report

You need a proc sort by Group, for a start.
Posts: 8,743

Re: Proc Report

You have several different choices:
1) proc print with the N option
2) DATA step to FILE PRINT and you would have to count the observations yourself

With option 1 and 2, you would probably need to sort the data beforehand, because you probably do not have just one main group, but want to get the count at the end of every set of groups of IDs.

The advantages and disadvantages of the above approaches are
1) Proc Print -- you have no control over the placement of the Count, although you can control some of the text that appears on the summary line -- since you'll need BY group processing if you have more than 1 group, the data must be sorted or indexed for PROC PRINT.;
2) You have a LOT of control over the output from DATA _NULL_ in LISTING, but must add everything up yourself (using RETAINed variables) and for other ODS Destinations (such as RTF and PDF and HTML), you can only produce tabular output with DATA _NULL_. Ditto the comment about sorting, if you have more than 1 group.
3) With PROC PRINT, you have a lot of control over how the COUNT at the end of the group looks, but the programming is a bit more complicated.

One thing that may be difficult for HTML, RTF, and PDF is your requirement to have the "APMP total is expanded to Sub-Group column". Generally, you can only do this kind of placement with a DATA _NULL_ step in the LISTING window or OUTPUT window. The thing is that when SAS builds TABULAR reports, it allots a certain amount of space to each column on the report and unless you are using DATA _NULL_ or PROC REPORT with a LINE statement (which spans all the columns), one column's information can not "infringe" on another columns allotted space.

But, consider that you had this data in a file called WORK.GRPDATA:
APMP A1 22121123
APMP B2 22111121
APMP C2 22111111
APMP C2 20112012
AXMP A1 32131123
AXMP B2 32311121
AXMP C2 32113131
AXMP C2 30113013

This PROC PRINT would give you the count you wanted underneath each main group, assuming the data was ordered or sorted by the GRP variable:

ods html file='print_sol.html'
proc print data=grpdata n="Number of IDs: " "Total: "
title 'simple proc print solution';
by grp;
sumby grp;
id grp;
var subgrp id;
ods html close;

The output would then look like this in the LISTING window (centering is turned off for this report):
simple proc print solution

Grp Subgrp ID

APMP A1 22121123
B2 22111121
C2 22111111
C2 20112012

Number of IDs: 4

AXMP A1 32131123
B2 32311121
C2 32113131
C2 30113013

Number of IDs: 4
Total: 8


To achieve results with the least programming learning curve, PROC PRINT is the quickest solution. Note how the label attached to N allows you to change the subtotal line for each group separately from the "grand total" line at the end of the report.

Ask a Question
Discussion stats
  • 2 replies
  • 2 in conversation