topic How to populate a variable in a previous row of a sas dataset in SAS Programming

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

ChiaraP — Wed, 09 Apr 2025 09:54:44 GMT

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

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

PaigeMiller — Wed, 09 Apr 2025 10:36:02 GMT

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.

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

Amir — Wed, 09 Apr 2025 10:44:41 GMT

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.