topic How to count the number of people who have the same diagnosis date (by ID and considering a visit #) in SAS Programming

How to count the number of people who have the same diagnosis date (by ID and considering a visit #)

awardell — Mon, 03 May 2021 01:14:46 GMT

I have a dataset that looks like the following table:

ID	DXDATE	VISIT_SEQUENCE	.... other different vars
1	12-03-2021	1
1	12-03-2021	2
2	06-04-2015	0
3	05-23-2020	1
3	05-23-2020	2
4	04-11-2019	1
4	07-24-2020	2

I need to count the number of individuals (determined by ID number) that have a sequence number 1 and 2 with the same diagnosis date. In this example, I would be looking to count 2 individuals.

I think this could be done by creating a data set with just these individuals and counting the first ID number, or a different way. Thank you for any help! I am fairly new to data sets with multiple rows for each ID.

Re: How to count the number of people who have the same diagnosis date (by ID and considering a visi

r_behata — Mon, 03 May 2021 01:53:16 GMT

One Way :

data have;
input ID $ DXDATE:mmddyy10.	VISIT_SEQUENCE;
format DXDATE mmddyy10.;
cards;
1 12-03-2021 1	 
1 12-03-2021 2	 
2 06-04-2015 0	 
3 05-23-2020 1	 
3 05-23-2020 2	 
4 04-11-2019 1	 
4 07-24-2020 2
;
run;

data want;
	merge have(in=a) have(in=b rename=(VISIT_SEQUENCE=_VISIT_SEQUENCE DXDATE=_DXDATE ) where=(_VISIT_SEQUENCE=2));
	by id;
	
	if a;
	
	if VISIT_SEQUENCE=1 and  DXDATE=_DXDATE then count=1;
	
	drop _:;
run;

Re: How to count the number of people who have the same diagnosis date (by ID and considering a visi

Reeza — Mon, 03 May 2021 03:12:44 GMT

This can get you started.

proc sql;
create table want as
select id, dxdate, sequence, count(distinct sequence) as num_sequences
from have
group by ID, dxdate
where visit_sequences in (1, 2);
quit;

Re: How to count the number of people who have the same diagnosis date (by ID and considering a visi

Astounding — Mon, 03 May 2021 06:19:47 GMT

I would use a tool that is built to count:

proc freq data=have noprint;
tables id * dxdate / out=want (drop=percent where=(count > 1) );
where visit_sequence in (1, 2);
run;

Whether or not you select this approach, it's a common tool that is worth learning.

Re: How to count the number of people who have the same diagnosis date (by ID and considering a visi

Ksharp — Mon, 03 May 2021 10:51:13 GMT

data have;
input ID $ DXDATE:mmddyy10.	VISIT_SEQUENCE;
format DXDATE mmddyy10.;
cards;
1 12-03-2021 1	 
1 12-03-2021 2	 
2 06-04-2015 0	 
3 05-23-2020 1	 
3 05-23-2020 2	 
4 04-11-2019 1	 
4 07-24-2020 2
;
run;

proc sql;
select count(distinct id) from
(
select id
 from have
  where VISIT_SEQUENCE ne 0
   group by id,dxdate
    having count(*)>1
);
quit;

Re: How to count the number of people who have the same diagnosis date (by ID and considering a visi

awardell — Tue, 04 May 2021 23:36:49 GMT

thank you! This worked well!

Re: How to count the number of people who have the same diagnosis date (by ID and considering a visi

awardell — Tue, 04 May 2021 23:37:06 GMT

Thank you! I will experiment in SQL more!

Re: How to count the number of people who have the same diagnosis date (by ID and considering a visi

awardell — Tue, 04 May 2021 23:37:26 GMT

Thank you! I had never heard of this feature! It will prove to be a handy tool!

Re: How to count the number of people who have the same diagnosis date (by ID and considering a visi

awardell — Tue, 04 May 2021 23:37:53 GMT

Thank you!! This method worked well!!