Help using Base SAS procedures

Trying to compare multiple records for the same ID across years

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

Trying to compare multiple records for the same ID across years

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.


Accepted Solutions
Solution
‎02-12-2015 05:28 AM
Super User
Posts: 10,019

Re: Trying to compare multiple records for the same ID across years

Posted in reply to Wolverine

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


All Replies
Solution
‎02-12-2015 05:28 AM
Super User
Posts: 10,019

Re: Trying to compare multiple records for the same ID across years

Posted in reply to Wolverine

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

Contributor
Posts: 42

Re: Trying to compare multiple records for the same ID across years

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.

Respected Advisor
Posts: 3,156

Re: Trying to compare multiple records for the same ID across years

Posted in reply to Wolverine

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

Are you sure?

Super User
Posts: 10,019

Re: Trying to compare multiple records for the same ID across years

Posted in reply to Wolverine

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

Contributor
Posts: 42

Re: Trying to compare multiple records for the same ID across years

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!

🔒 This topic is solved and locked.

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

Discussion stats
  • 5 replies
  • 308 views
  • 0 likes
  • 3 in conversation