topic Re: Distinct Count in SAS Programming

Distinct Count

anonymous_user — Tue, 14 Dec 2021 14:40:42 GMT

Re: Distinct Count

Kurt_Bremser — Wed, 01 Dec 2021 08:39:01 GMT

Please supply a usable example (data step with datalines) of your source data.

Re: Distinct Count

anonymous_user — Wed, 01 Dec 2021 16:58:23 GMT

proc sql;
create table EMsummary as
select
cpt,
real_date as year_qtr,
count(distinct memberNo) as member_dist,
count(memberNo) as memberct,
memberNo,
cost,
sum(cost) as totalcost
from EMpull
where (cpt between '99201' and '99215')
or (cpt between '99241' and '99245')
or (cpt between '99381' and '99397')
or (cpt between '99354' and '99355')
or (cpt between '99401' and '99412')
group by year_qtr;
quit;

/*this should be distinct*/

proc means data=EMsummary noprint nway;
class year_qtr cpt;
var memberdist cost;
output out=EMsummary2a sum = / autoname;
run;

Re: Distinct Count

Kurt_Bremser — Wed, 01 Dec 2021 18:20:17 GMT

DATA step with DATALINES.

Re: Distinct Count

anonymous_user — Wed, 01 Dec 2021 19:13:46 GMT

I created this because I dont have data step with datalines in my program:

data distinctmember;
input year_qtr cpt memberct_dist cost;
datalines;
2021Q1 99214 5 $1,630.22
2021Q1 99215 8 $1,520.36

2021Q2 99212 2 $785.00

2021Q3 99212 3 $561.30
;

Re: Distinct Count

LinusH — Thu, 02 Dec 2021 07:40:19 GMT

Sorry, I might miss the obvious, but why don't you use distinct count in SQL?

Re: Distinct Count

Kurt_Bremser — Thu, 02 Dec 2021 09:06:09 GMT

I obviously forgot to mention that the data step should be working:

 69         data distinctmember;
 70         input year_qtr cpt memberct_dist cost;
 71         datalines;
 
 NOTE: Invalid data for year_qtr in line 72 1-6.
 NOTE: Invalid data for cost in line 72 21-29.
 REGEL:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0                     
 72         2021Q1  99214   5   $1,630.22
 year_qtr=. cpt=99214 memberct_dist=5 cost=. _ERROR_=1 _N_=1
 NOTE: Invalid data for year_qtr in line 73 1-6.
 NOTE: Invalid data for cost in line 73 21-29.
 73         2021Q1  99215   8   $1,520.36
 year_qtr=. cpt=99215 memberct_dist=8 cost=. _ERROR_=1 _N_=2
 NOTE: Invalid data for year_qtr in line 74 1-6.
 NOTE: Invalid data for cost in line 74 24-30.
 74         2021Q2  99212   2      $785.00
 year_qtr=. cpt=99212 memberct_dist=2 cost=. _ERROR_=1 _N_=3
 NOTE: Invalid data for year_qtr in line 75 1-6.
 NOTE: Invalid data for cost in line 75 24-30.
 75         2021Q3 99212    3      $561.30
 year_qtr=. cpt=99212 memberct_dist=3 cost=. _ERROR_=1 _N_=4

Re: Distinct Count

anonymous_user — Mon, 06 Dec 2021 19:34:11 GMT

it didnt come out right when i did that:; I'm trying to have it look like this:

sorted by code and quarter with unique member number count under each column for each code.

Code Q1 Q2 Q3 ....

99251 # of unique members .......................