select observations based on initial visit

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

select observations based on initial visit

Hi

 

I want to select observations based on the initial visit or the date of diagnosis. 

Patient ID

Date of Visit

Diagnosis code 1

Diagnosis Code 2

A

0

0881

0981

A

2

0981

0234

A

5

0234

0567

A

30

0881

.

B

0

0881

0781

B

3

0881

.

B

9

0789

0456

B

45

0781

.

 

In the above table Day 0 is the initial visit or date of dignosis for two patients A and B. A got the diagnosis codes 0881 and 0981 on initial visit. I want to select all subsequent visists of A when he had either of those codes (i.e. Day 2 and 30 selected but not day 5) . Similiarly B had diagnosis codes 0881 and 0781 on the day of diagnosis, I want to select subsequent visits where either of theses codes appear (days 3 and 45 but not 9). Is there a programing short cut or procedure to check a unique criteria for each patient in a dataset? i.e. create a crietia for patient A and B and teh check it against subsquenet visits??

 

Thanks

Manjunath


Accepted Solutions
Solution
‎12-26-2015 11:44 AM
Respected Advisor
Posts: 4,825

Re: select observations based on initial visit

For any number of patientId, visits or diagnosis codes :

 

data have;
input PatientID $	DateOfVisit	DiagnosisCode1	DiagnosisCode2;
format DiagnosisCode: z4.0;
datalines;
A	0	0881	0981
A	2	0981	0234
A	5	0234	0567
A	30	0881	.
B	0	0881	0781
B	3	0881	.
B	9	0789	0456
B	45	0781	.
;

proc transpose data=have out=temp prefix=Diag;
var DiagnosisCode:;
by patientId DateOfVisit;
run;

proc sql;
create table firstVisits as
select patientId, diag1
from temp
group by patientId
having DateOfVisit = min(DateOfVisit);
create table sameDiagVisits as
select unique PatientId, DateOfVisit
from temp as a
where diag1 in (
    select diag1 
    from firstVisits
    where PatientId=a.patientId);
create table want as
select a.* 
from have as a natural inner join sameDiagVisits
order by PatientId, DateOfVisit;
select * from want;
quit;
PG

View solution in original post


All Replies
Solution
‎12-26-2015 11:44 AM
Respected Advisor
Posts: 4,825

Re: select observations based on initial visit

For any number of patientId, visits or diagnosis codes :

 

data have;
input PatientID $	DateOfVisit	DiagnosisCode1	DiagnosisCode2;
format DiagnosisCode: z4.0;
datalines;
A	0	0881	0981
A	2	0981	0234
A	5	0234	0567
A	30	0881	.
B	0	0881	0781
B	3	0881	.
B	9	0789	0456
B	45	0781	.
;

proc transpose data=have out=temp prefix=Diag;
var DiagnosisCode:;
by patientId DateOfVisit;
run;

proc sql;
create table firstVisits as
select patientId, diag1
from temp
group by patientId
having DateOfVisit = min(DateOfVisit);
create table sameDiagVisits as
select unique PatientId, DateOfVisit
from temp as a
where diag1 in (
    select diag1 
    from firstVisits
    where PatientId=a.patientId);
create table want as
select a.* 
from have as a natural inner join sameDiagVisits
order by PatientId, DateOfVisit;
select * from want;
quit;
PG
Occasional Contributor
Posts: 5

Re: select observations based on initial visit

Hi
Thanks for the prompt response. This seems to be working. I just made one change "Dateofvisit=0 " instead of minimum because my dataset has negative values for visits before the diagnosis in question. Thanks for the awesome solution.
Kind Regards
Manjunath
Super User
Posts: 9,878

Re: select observations based on initial visit

data have;
infile cards truncover expandtabs;
input PatientID $	DateOfVisit	DiagnosisCode1	DiagnosisCode2;
format DiagnosisCode: z4.0;
datalines;
A	0	0881	0981
A	2	0981	0234
A	5	0234	0567
A	30	0881	.
B	0	0881	0781
B	3	0881	.
B	9	0789	0456
B	45	0781	.
;
run;
data want;
 set have;
 by PatientID ;
 array x{9999} _temporary_;
 array d{*} DiagnosisCode: ;
 if first.PatientID  then do;
  do i=1 to dim(d);
   x{i}=d{i};
  end;
 end;
 else do;
  do i=1 to dim(d);
   if not missing(d{i}) and d{i} in x then output;
  end;
 end;
drop i;
run;
Respected Advisor
Posts: 4,825

Re: select observations based on initial visit

@Ksharp, you will get duplicates if many diagnosis from the initial visit appear in a subsequent visit. I propose instead:

 

data want;
 set have;
 by PatientID ;
 array x{9999} _temporary_;
 array d{*} DiagnosisCode: ;
 if first.PatientID  then do;
  do i=1 to dim(d);
   x{i}=d{i};
  end;
 end;
 else do;
  do i=1 to dim(d);
   if not missing(d{i}) and d{i} in x then leave;
  end;
  if i <= dim(d) then output;
 end;
drop i;
run;
PG
Super User
Posts: 9,878

Re: select observations based on initial visit

PG,

No. This could be better.

 

data want;
 set have;
 by PatientID ;
 array x{9999} _temporary_;
 array d{*} DiagnosisCode: ;
 if first.PatientID  then do;
  do i=1 to dim(d);
   x{i}=d{i};
  end;
 end;
 else do;
  do i=1 to dim(d);
   if not missing(d{i}) and d{i} in x then do;output; leave;end;
  end;
 end;
drop i;
run;
Contributor
Posts: 62

Re: select observations based on initial visit

data have;
infile cards truncover expandtabs;
input PatientID $	DateOfVisit	D1	D2;
format DiagnosisCode: z4.0;
datalines;A	0	0881	0981
A	2	0981	0234
A	5	0234	0567
A	30	0881	.
B	0	0881	0781
B	3	0881	.
B	9	0789	0456
B	45	0781	.;



data t1;
set have;
by
PatientID ;
retain x y;
if first.patientid then do;
x=d1;y=d2;
end;
if d1=x or d1=y or d2=x or d2=y;
drop x y;
run;
proc print;run;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 457 views
  • 3 likes
  • 4 in conversation