BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Hi,

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

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


2)
Group | Sub-Group | ID | Count
--------------------------------------
APMP | A1 | 22121123 |
APMP | B2 | 22111121 |
APMP | C2 | 22111111 |
APMP | C2 | 20112012 | 4
2 REPLIES 2
deleted_user
Not applicable
You need a proc sort by Group, for a start.
Cynthia_sas
SAS Super FREQ
Hi:
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
3) PROC REPORT

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:
[pre]
GRP SUBGRP ID
APMP A1 22121123
APMP B2 22111121
APMP C2 22111111
APMP C2 20112012
AXMP A1 32131123
AXMP B2 32311121
AXMP C2 32113131
AXMP C2 30113013
[/pre]

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:
[pre]

ods html file='print_sol.html'
style=sasweb;
proc print data=grpdata n="Number of IDs: " "Total: "
noobs;
title 'simple proc print solution';
by grp;
sumby grp;
id grp;
var subgrp id;
run;
ods html close;
[/pre]

The output would then look like this in the LISTING window (centering is turned off for this report):
[pre]
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

[/pre]

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.

cynthia

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 567 views
  • 0 likes
  • 2 in conversation