Hello all! I have a question about how to create a variable that compares numbers across rows.
Below is my start dataset.
id Start_Date Med Dose
1 1/20/15 A 5
1 3/1/15 B 10
1 4/30/15 A 10
1 7/10/15 A 5
1 9/15/15 A 2.5
1 10/10/15 A 5
2 3/14/18 C 50
2 4/01/18 C 25
Each id has received different types of medications. Each date that a person had a medication or dose change is shown in start_date. For any date that a certain medication had a dose change, I would like to assign +1 if the dose was increased or -1 if the dose was decreased. (Example: id 1 had A increased from 5 to 10 on 4/30/15, so on that date, dosechange should be 1. On 7/10/15 the A dose was decreased back to 5 so dosechange should be -1.) If the person was only added on to a new med (eg, id 1 started on B on 3/1/15), I would like to just keep dose change as 0.
Below is what I would like:
id Start_Date Med Dose DoseChange
1 1/20/15 A 5 0
1 3/1/15 B 10 0
1 4/30/15 A 10 1
1 7/10/15 A 5 -1
1 9/15/15 A 2.5 -1
1 10/10/15 A 5 1
2 3/14/18 C 50 0
2 4/01/18 C 25 -1
Any advice?
Thank you!
data have;
input id Start_Date :mmddyy8. Med $ Dose;
format start_date mmddyy8.;
cards;
1 1/20/15 A 5
1 3/1/15 B 10
1 4/30/15 A 10
1 7/10/15 A 5
1 9/15/15 A 2.5
1 10/10/15 A 5
2 3/14/18 C 50
2 4/01/18 C 25
;
data want;
if _n_=1 then do;
length _dose 8;
if 0 then set have ;
declare hash H ( ) ;
h.definekey ("id",'med') ;
h.definedata ("_dose") ;
h.definedone () ;
call missing(_dose);
end;
set have;
by id start_date;
dosechange=0;
if h.find() ne 0 then h.add(key:id,key:med,data:dose);
else do;
if dose>_dose then dosechange=1;
else if dose<_dose then dosechange=-1;
h.replace(key:id,key:med,data:dose);
end;
drop _dose;
run;
data have;
input id Start_Date :mmddyy8. Med $ Dose;
format start_date mmddyy8.;
cards;
1 1/20/15 A 5
1 3/1/15 B 10
1 4/30/15 A 10
1 7/10/15 A 5
1 9/15/15 A 2.5
1 10/10/15 A 5
2 3/14/18 C 50
2 4/01/18 C 25
;
data want;
if _n_=1 then do;
length _dose 8;
if 0 then set have ;
declare hash H ( ) ;
h.definekey ("id",'med') ;
h.definedata ("_dose") ;
h.definedone () ;
call missing(_dose);
end;
set have;
by id start_date;
dosechange=0;
if h.find() ne 0 then h.add(key:id,key:med,data:dose);
else do;
if dose>_dose then dosechange=1;
else if dose<_dose then dosechange=-1;
h.replace(key:id,key:med,data:dose);
end;
drop _dose;
run;
Thank you all!
I tried all 3 codes (from novinosrin, ChrisNZ, and Patrick). The only difference in the results was that ChrisNZ's code made the change a missing value for the first dose before it was changed, while both novinosrin and Patrick's code set the first dose to 0, which is what I was looking for. But ultimately, the other results (from all 3 of you) were exactly what I was looking for.
I selected novinosrin's as the solution since it was the first one posted and gave me exactly what I needed. But thank you all! 🙂
Like this?
data HAVE; format START_DATE date9.;
input id START_DATE mmddyy9. Med $ Dose ;
cards;
1 1/20/15 A 5
1 3/1/15 B 10
1 4/30/15 A 10
1 7/10/15 A 5
1 9/15/15 A 2.5
1 10/10/15 A 5
2 3/14/18 C 50
2 4/01/18 C 25
run;
proc sort data=HAVE out=SORTED;
by ID MED START_DATE;
run;
data CHANGES;
set SORTED;
by ID MED START_DATE;
CHANGE=^first.MED*sign(dif(DOSE));
run;
proc sort data=CHANGES out=WANT;
by ID START_DATE MED ;
run;
START_DATE | id | Med | Dose | CHANGE |
---|---|---|---|---|
20JAN2015 | 1 | A | 5.0 | . |
01MAR2015 | 1 | B | 10.0 | 0 |
30APR2015 | 1 | A | 10.0 | 1 |
10JUL2015 | 1 | A | 5.0 | -1 |
15SEP2015 | 1 | A | 2.5 | -1 |
10OCT2015 | 1 | A | 5.0 | 1 |
14MAR2018 | 2 | C | 50.0 | 0 |
01APR2018 | 2 | C | 25.0 | -1 |
Something like below should do.
data have;
infile datalines dlm=' ' truncover;
input id Start_Date:mmddyy. Med $ Dose;
format start_date date9.;
datalines;
1 1/20/15 A 5
1 3/1/15 B 10
1 4/29/15 A 10
1 4/30/15 A 10
1 7/10/15 A 5
1 9/15/15 A 2.5
1 10/10/15 A 5
2 3/14/18 C 50
2 4/01/18 C 25
2 4/02/18 C .
2 4/03/18 C 25
2 4/04/18 C 26
;
run;
proc sort data=have out=want;
by id med Start_Date;
run;
proc format;
invalue DoseChange
low -< 0 = -1
0 = 0
0 <- high = 1
;
quit;
data want;
set want;
by id med start_date;
DoseChange= input((dose-lag(dose)),DoseChange.);
if first.med then DoseChange=0;
run;
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 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.