How to populate a variable in a previous row of a sas dataset

[ChiaraP]

Dear

I have a dataset with the following variables: patient ID and dose.

I would like to create a new variable called increase based on the Dose variable.

This variable should be set to 1 when the dose in the next row (for the same patient) is higher.

Example 

pt_ID  DOSE   increase

1             3               1

1             4               0

1             3               0

2            24             0

2            22             1

2            25             0

Thank in advance for any help

[PaigeMiller · Accepted solution]

data have;
input pt_ID  DOSE;
datalines;
1             3
1             4
1             3
2            24
2            22
2            25
;

data want;
    merge have have(firstobs=2 keep=dose pt_id rename=(dose=next_dose pt_id=next_pt_id));
    increase=0;
    if pt_id=next_pt_id and next_dose>dose then increase=1;
    drop next_:;
run;

Please from now on, provide data as data step code, as I show above. Please do not provide data in other forms.

[Amir]

Hi,

I had essentially the same solution as @PaigeMiller, with a slight difference in the method used to assign the increase variable.

data have;
  input
    pt_id
    dose
  ;
  
  datalines;
1 3
1 4
1 3
2 24
2 22
2 25
;


data want(drop = next:);
  merge
    have 
    have(firstobs=2 rename = (pt_id = next_pt_id dose = next_dose))
  ;
  
  increase = next_pt_id eq pt_id and next_dose gt dose;
run;

Thanks & kind regards,

Amir.