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
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;
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;
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;
@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,
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;
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;
byPatientID
;
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;
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.