BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Hi,

Can anyone explain how to code count(distinct) in proc report ?

Amit
5 REPLIES 5
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Explain what you would like to see in your PROC REPORT output - a sample report illustration in your post might be helpful.

Also, might I recommend using the SAS support website http://support.sas.com/ and I did the Google advanced search below, which revealed some matches using your keyword references:

+"proc report" +distinct +count site:sas.com

Have a look at the COMPUTE block to count BEFORE/AFTER a unique value

Scott Barry
SBBWorks, Inc.
deleted_user
Not applicable
Let say, A monthly transaction dataset contains customer account number and account balance.This dataset may have multiple occurrences of an account number. How should i generate a report having total balance of only unique account numbers

Does it make sense now ?

Amit
Cynthia_sas
SAS Super FREQ
Hi:
As Scott hinted, with PROC REPORT, you would want a SUMMARY report (where each report row represented the "collapsing" or "summarization" of all the observations for a unique GROUP variable (or combination of GROUP/ORDER variables)).

For example, with this data:
[pre]data bankinfo;
infile datalines;
input acct amt;
return;
datalines;
123 100
123 150
123 300
456 200
456 200
890 100
890 100
890 100
890 50
991 100
991 250
;
run;

[/pre]

There are 11 observations in the data set, but only 4 unique account numbers.

So, this PROC REPORT, will produce a "summary" report:
[pre]
ods listing close;
ods html file='bankinfo.html' style=sasweb;
proc report data=bankinfo nowd;
column acct amt n;
define acct / group;
define amt / sum f=comma6. "Balance Amt";
define n / 'Number of Obs';
rbreak after / summarize;
compute acct;
if _break_ = ' ' then cnt_uniq + 1;
endcomp;
compute after /
style={just=l};
line 'Number of Accounts:' cnt_uniq 2.0;
endcomp;
run;
ods html close;
[/pre]

.... that has one report row for every unique value of account number. The RBREAK AFTER statement will put a "grand total" summary line at the bottom of the report. The LINE statement inside the COMPUTE block adds an explanatory line to the bottom of the report that shows what the total count of unique account numbers is. The CNT_UNIQ temporary variable is used to hold a "running" count of unique account numbers.

cynthia
deleted_user
Not applicable
This ould be coded in Proc SQL something like this :

proc sql;
create table a xyz as
select count(distinct acct) as accnum,
sum(balance) as balance

from abc;
quit
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Sounds like you want a summary reporting. Have a look at the GROUP option on the DEFINE statement.

Have a look at the search option on the SAS support website http://support.sas.com/ or a Google advanced search using the keywords below will yield several matches with SAS DOC, technical papers (SGF, SUGI user conference).

+"proc report" +summary site:sas.com


Scott Barry
SBBWorks, Inc.

PROC REPORT DOC link:
http://support.sas.com/onlinedoc/913/getDoc/en/proc.hlp/a000068725.htm

SUGI/SGF technical paper on PROC REPORT:
http://www2.sas.com/proceedings/forum2007/242-2007.pdf

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 5 replies
  • 3209 views
  • 0 likes
  • 3 in conversation