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.
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
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
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.
"but the output file was identical to the input file".
Are you sure?
Didn't you notice I added a new column "newdevice" into it ?
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!
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 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.