Moving observation up and down within row by patient ID (data with several rows per patient)

[js1983]

Hi there, first of all - thank you for looking at my post and for helping me organize my data. 

I have a sas data file with the following structure (help.have): what I need to do is to move the covariates 'phosphorus' and 'calcium' to the next month within the respective Patient ID and to set the values in month 0 equal to the baseline values. I also need to shift the outcome to the previous month and set the outcome in the last month to 'censored=a'. I need to do this in order to run an weighted regression model in the end. In the end the dataset should look like (help.want).  

I am using SAS 9.4.

I am not sure with what approach to go about this, so I haven't tried anything yet. 

Thanks a lot for your inputs - any ideas are welcome. 

data help.have;
  infile datalines dsd truncover;
  input PatID exposure month phosphate calcium phos_baseline calc_baseline outcome;
datalines4;
1,2,0,2.5,13,2.6,14,0, 
1,2,1,2.7,11,2.6,14,0,
1,2,2,2.6,10,2.6,14,0,
1,2,3,2.4,10,2.6,14,0,
2,1,0,2.2,11,2.5,12,0,
2,1,1,2.1,10,2.5,12,0, 
2,1,2,2.0,9.5,2.5,12,1, 
3,2,0,2.8,12,2.2,9,0, 
3,2,1,2.7,11,2.2,9,0, 
3,2,2,2.9,12,2.2,9,0, 
3,2,3,3.0,10,2.2,9,0 
;;;;

data help.want;
  infile datalines dsd truncover;
  input PatID exposure month phosphate calcium outcome;
datalines4;
1,2,0,2.6,14,2.6,14,0  
1,2,1,2.5,13,2.6,14,0  
1,2,2,2.7,11,2.6,14,0  
1,2,3,2.6,10,2.6,14,0  
2,1,0,2.5,12,2.5,12,0  
2,1,1,2.2,11,2.5,12,1 
2,1,2,2.1,10,2.5,12,a 
3,2,0,2.2,9,2.2,9,0 
3,2,1,2.8,12,2.2,9,0 
3,2,2,2.7,11,2.2,9,0 
3,2,3,2.9,12,0 
;;

[SuryaKiran]

You mentioned next values but your want dataset shows previous values. You can use LAG() function for previous values. 

data want (keep=PatID exposure month phosphate calcium outcome);
set have;
lag_ph=lag(phosphate);
lag_cal=Lag(calcium);
if month=0 then do;
  phosphate=phos_baseline;
  calcium=calc_baseline;
  end;
else do;
  phosphate=lag_ph;
  calcium=lag_cal;
  end;
run;

I didn't understand your logic for populating outcome. You can alter this code per your requirement.

[js1983]

Thanks a lot SuriyaKiran, 

I will give this a go asap!

[js1983]

Your code worked very well - thanks a lot SuryaKiran for your help that would have taken me a long time to figure out!

With regard to the outcome variable, I need to shift it in the opposite direction: i.e. outcome in month 0 will be deleted, outcome in month 1 will be moved to 0 etc. and outcome in the last month is set to missing (.). 

I suspect the lag function only works in the other direction - is there any commands to do what I need to do? 

Thanks again!  

[SuryaKiran · Accepted solution]

Like LAG() function there is no LEAD function in SAS becuase SAS reads data sequentially can't read the next records while reading the current record. For more information check here. 

You can achieve this by merging the same dataset. 

proc sort data=have;
by patid month;
run;
data want (keep=PatID exposure month phosphate calcium outcome Outcome_);
Merge have have(firstobs=2 rename=(Outcome=Outcome_) keep=outcome);
lag_ph=lag(phosphate);
lag_cal=Lag(calcium);
if month=0 then do;
  phosphate=phos_baseline;
  calcium=calc_baseline;
  call missing(Outcome);
  end;
else do;
  phosphate=lag_ph;
  calcium=lag_cal;
  outcome=outcome_;
  end;
if last.patid then call missing(outcome);
run;

[js1983]

Thank you so much once again SuryaKiran, that was very helpful. I was able to get my data in shape with your recommendations of merging two datasets - I just had to tweak it slightly to fit my data. 

Thanks again for looking into my data issues - this is the code I ended up using. 

proc sort data=have
by patid month;
run;
data want (keep=patID exposure month phosphorus calcium_corrected recent_phosphorus recent_CA_corrected outcome Outcome_);
Merge have have (firstobs=2 keep=outcome USRDS_ID rename=(outcome=Outcome_ patID=patID2)); 
lag_ph=lag(phosphorus);
lag_cal=lag(calcium_corrected);
if month=0 then do;
phosphorus=recent_phosphorus;
calcium=recent_CA_corrected;
outcome=Outcome_;
end;
else do;
phosphorus=lag_ph;
calcium_corrected=lag_cal;
outcome=Outcome_;
if patID ne patID2 then outcome=.;
end;
run;