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

[awardell]

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. 

[r_behata · Accepted solution]

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;

[awardell]

thank you! This worked well!

[Reeza]

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;

[awardell]

Thank you! I will experiment in SQL more!

[Astounding]

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.

[awardell]

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

[Ksharp]

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;

[awardell]

Thank you!! This method worked well!!