BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
manjubs
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

6 REPLIES 6
PGStats
Opal | Level 21

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
manjubs
Fluorite | Level 6
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
Super User
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
Opal | Level 21

@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
Ksharp
Super User

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
Fluorite | Level 6
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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