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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20
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;





 

View solution in original post

4 REPLIES 4
novinosrin
Tourmaline | Level 20
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;





 
heretolearn
Obsidian | Level 7

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! 🙂

ChrisNZ
Tourmaline | Level 20

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
Patrick
Opal | Level 21

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;
  

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!

How to Concatenate Values

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.

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
  • 4 replies
  • 468 views
  • 3 likes
  • 4 in conversation