topic Re: Trying to compare multiple records for the same ID across years in SAS Procedures

Trying to compare multiple records for the same ID across years

Wolverine — Wed, 11 Feb 2015 15:20:42 GMT

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:

ID	device_measyr	device_yrprior	newdevice
5626	2	4	1
7559	1	4	1
7559	4	1	1
8230	1	2	1
8230	1	4	1
8230	2	4	1
8230	4	2	1

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.

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

Ksharp — Thu, 12 Feb 2015 10:28:56 GMT

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

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

Wolverine — Fri, 13 Feb 2015 13:53:57 GMT

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.

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

Haikuo — Fri, 13 Feb 2015 23:59:17 GMT

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

Are you sure?

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

Ksharp — Sat, 14 Feb 2015 07:23:56 GMT

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

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

Wolverine — Mon, 16 Feb 2015 16:45:00 GMT

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!