Creating new variable that compares numbers across rows

[heretolearn]

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!

[novinosrin · Accepted solution]

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]

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]

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]

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;