topic Re: Count the number of individuals with multiple observations in SAS Programming

Count the number of individuals with multiple observations

Cruise — Thu, 16 Jan 2020 15:39:44 GMT

Hi folks:

I'd like to know how many patients were contacted more than one time. In the mock data, there are two patients and the first patient has two different dates (1 and 2) compared to the second patient who has the same date_contact for both rows. The correct answer to this is 1. Only one patient had two different dates which is indicating that he/she was contacted for two times. However, my proc sql in the code shown outputs 2 which is wrong.

Can you please help me correct my code or suggest a better approach to solve this problem?

Thanks in advance.

data temp;
input ctc_id date_contact; 
cards;
1 1
1 2
2 1
2 1
;

PROC SORT DATA=TEMP;
BY CTC_ID;
DATA TEMP1; SET TEMP; 
by CTC_ID;
if first.CTC_ID then ID + 1;
RUN; 

PROC SORT DATA=TEMP1;
BY CTC_ID DATE_CONTACT;
DATA TEMP2; SET TEMP1; 
by CTC_ID DATE_CONTACT;
if first.DATE_CONTACT then ID1 + 1;
RUN;

DATA TEMP3; SET TEMP2;
IF ID NE ID1; 
RUN;

PROC SQL; SELECT COUNT(DISTINCT CTC_ID) FROM TEMP3; quit;

Re: Count the number of individuals with multiple observations

novinosrin — Thu, 16 Jan 2020 15:44:39 GMT

Hi @Cruise if I understand you correctly, you just need this condition

having count(distinct date_contact)>1;

I.e


data temp;
input ctc_id date_contact; 
cards;
1 1
1 2
2 1
2 1
;

proc sql;
select *
from temp
group ctc_id
having count(distinct date_contact)>1;
quit;

And if you want to count the indiv's

You could


proc sql;
select count(distinct ctc_id) as count label='num of indivs with multiple'
from temp
group ctc_id
having count(distinct date_contact)>1;
quit;

Re: Count the number of individuals with multiple observations

PaigeMiller — Thu, 16 Jan 2020 15:44:27 GMT

proc sql;
     create table temp1 as select distinct ctc_id,date_contact from temp;
run;
proc freq data=temp1;
    tables ctc_id/noprint out=temp2;
run;
proc freq data=temp2(where=(count>1));
    tables ctc_id;
run;

Re: Count the number of individuals with multiple observations

Cruise — Thu, 16 Jan 2020 16:16:22 GMT

@novinosrin

very nice. thanks.

i added one more patient just wondering of my mock data was too simple. but now, the count is output in two separate rows. how to get the sum?

data temp;
input ctc_id date_contact; 
cards;
1 1
1 2
2 1
2 1
3 1
3 2
;

proc sql;
select count(distinct ctc_id) as count label='num of indivs with multiple'
from temp
group ctc_id
having count(distinct date_contact)>1;
quit;

Re: Count the number of individuals with multiple observations

novinosrin — Thu, 16 Jan 2020 16:22:25 GMT

Hi Again @Cruise First off , my sincere apologies for overlooking a very minor logic, albeit that's not an excuse.

Try the below modified

data temp;
input ctc_id date_contact; 
cards;
1 1
1 2
2 1
2 1
3 1
3 2
;

proc sql;
create table want as
select count( ctc_id) as count label='num of indivs with multiple'
from
(select distinct ctc_id from temp group ctc_id having count(distinct date_contact)>1);
quit;