select observations based on initial visit

[manjubs]

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

[PGStats · Accepted solution]

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;

[manjubs]

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

[Ksharp]

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;

[PGStats]

@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;

[Ksharp]

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;

[pawandh]

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;