topic Re: Proc Report : how to get distinct count of a variable in ODS and Base Reporting

Proc Report : how to get distinct count of a variable

deleted_user — Thu, 09 Oct 2008 13:53:20 GMT

Hi,

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

Amit

Re: Proc Report : how to get distinct count of a variable

sbb — Thu, 09 Oct 2008 14:08:25 GMT

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.

Re: Proc Report : how to get distinct count of a variable

deleted_user — Thu, 09 Oct 2008 14:21:06 GMT

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

Re: Proc Report : how to get distinct count of a variable

deleted_user — Thu, 09 Oct 2008 14:26:05 GMT

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

Re: Proc Report : how to get distinct count of a variable

sbb — Thu, 09 Oct 2008 15:09:10 GMT

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

Re: Proc Report : how to get distinct count of a variable

Cynthia_sas — Fri, 10 Oct 2008 01:10:46 GMT

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