topic Re: Count distinct values by subjects in Statistical Procedures

Count distinct values by subjects

Neff — Sun, 17 Jun 2018 21:15:58 GMT

I have this large data set comprising of over 1000 subjects and about 3000 variables which are ICD9 codes (Dx1- Dx3000).

Dx represents the ICD diagnosis codes for each visit and is represented here as 1-4

My dataset looks similar to this:

I would like to get the distinct disease count for each subject.

For example: subject 1 has 4 visits but 3 distinct diagnosis.

subject 2 has 4 visits and 4 distinct diagnosis

Thanks.

Count distinct values by subjects

Neff — Sun, 17 Jun 2018 21:15:21 GMT

I have this large data set comprising of over 1000 subjects and about 3000 variables which are ICD9 codes (Dx1- Dx3000).

Dx represents the ICD diagnosis codes for each visit and is represented here as 1-4

My dataset looks similar to this:

I would like to get the distinct disease count for each subject.

For example: subject 1 has 4 visits but 3 distinct diagnosis.

subject 2 has 4 visits and 4 distinct diagnosis

Thanks.

Re: Count distinct values by subjects

novinosrin — Sun, 17 Jun 2018 21:29:07 GMT

1. proc transpose

2. proc freq by id

Re: Count distinct values by subjects

PGStats — Sun, 17 Jun 2018 22:00:18 GMT

proc transpose data=myData out=codes;
by id;
var dx1-dx3000;
run;

proc sql;
create table counts as
select id, count(distinct col1) as nbCodes
from codes
group by id;
quit;

(untested)

Re: Count distinct values by subjects

Neff — Sun, 17 Jun 2018 23:50:43 GMT

Thank you so much!! Just what I needed and it worked perfectly well