Creating new variable that compares numbers across rows

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Creating new variable that compares numbers across rows

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!


Accepted Solutions
Solution
‎06-20-2018 12:55 AM
PROC Star
Posts: 1,845

Re: Creating new variable that compares numbers across rows

Posted in reply to heretolearn
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


All Replies
Solution
‎06-20-2018 12:55 AM
PROC Star
Posts: 1,845

Re: Creating new variable that compares numbers across rows

Posted in reply to heretolearn
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;





 
Occasional Contributor
Posts: 15

Re: Creating new variable that compares numbers across rows

Posted in reply to novinosrin

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! Smiley Happy

PROC Star
Posts: 2,375

Re: Creating new variable that compares numbers across rows

Posted in reply to heretolearn

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
Respected Advisor
Posts: 4,743

Re: Creating new variable that compares numbers across rows

Posted in reply to heretolearn

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;
  
☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 104 views
  • 3 likes
  • 4 in conversation