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;
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;
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;
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.