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

I'm trying to identify kids with asthma who have received a new delivery device for their medication across years (the measurement year and the year prior to the measurement year).  The problem occurs when a kid has more than 1 type of delivery device (there are 5 categories of devices).  Consider the example data:

IDdevice_measyrdevice_yrpriornewdevice
5626241
7559141
7559411
8230121
8230141
8230241
8230421

ID# 5626 only had 1 device, and it was different across years.  So that kid really did get a new device in the measurement year, and newdevice should = 1.  But 7559 had a 1 and a 4 in the year prior, and a 1 and a 4 in the meas year, so 7559 is not a valid case and should be deleted.

8230 is a little more complicated.  This kid had 2 and 4 in both years, but had 1 in the meas year ONLY.  Therefore 8230 is a valid case, and newdevice should = 1.

I should note that the individual records don't matter -- if a kid got ANY new device in the meas year, then that ID should be retained.

It seems like this should be pretty easy using PROC SQL to select distinct IDs, but I'm not quite sure how to get it to compare all values for all records for each ID.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

If I understood what you mean, But I prefer to Hash Table.

data have;
input ID     device_measyr     device_yrprior;     
cards;
5626     2     4     
7559     1     4     
7559     4     1     
8230     1     2     
8230     1     4     
8230     2     4     
8230     4     2
;
run;
proc sql;
 create table want as
  select *,
case when count(*)=1 then 1
 when exists(select * from have  where id=a.id and device_measyr not in (select device_yrprior from have where id=a.id )) 
      then 1 
     else 0 
end as newdevice
   from have as a
    group by id ;
quit;

Xia Keshan

View solution in original post

5 REPLIES 5
Ksharp
Super User

If I understood what you mean, But I prefer to Hash Table.

data have;
input ID     device_measyr     device_yrprior;     
cards;
5626     2     4     
7559     1     4     
7559     4     1     
8230     1     2     
8230     1     4     
8230     2     4     
8230     4     2
;
run;
proc sql;
 create table want as
  select *,
case when count(*)=1 then 1
 when exists(select * from have  where id=a.id and device_measyr not in (select device_yrprior from have where id=a.id )) 
      then 1 
     else 0 
end as newdevice
   from have as a
    group by id ;
quit;

Xia Keshan

Wolverine
Quartz | Level 8

Unfortunately that didn't work.  It didn't produce any errors in the log, but the output file was identical to the input file.  If you prefer to use a hash table rather than Proc SQL, that's fine with me.

Haikuo
Onyx | Level 15

"but the output file was identical to the input file".

Are you sure?

Ksharp
Super User

Didn't you notice I added a new column  "newdevice"  into it ?

Wolverine
Quartz | Level 8

It's working now!  Yes, the output file was identical to the input file.  Note that the portion of the input file that I used as an example in my original post already includes a column named "newdevice".  That's because there was an IF-THEN statement in the step that created the input file that calculated the value for newdevice for all cases that only had 1 device in the year prior.

It seems like the Proc SQL subroutine should have overwritten the value of newdevice for cases where there was more than 1 device, but it did not.  So I simply moved the IF-THEN statement to AFTER the Proc SQL.  So the Proc SQL took care of the case with more than 1 device, and then the IF-THEN statement took care of cases that only had 1 device.

Thanks for your help!

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 2448 views
  • 0 likes
  • 3 in conversation