topic Re: Moving observation up and down within row by patient ID (data with several rows per patient) in SAS Data Management

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

js1983 — Thu, 31 May 2018 12:45:54 GMT

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 
;;

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

SuryaKiran — Thu, 31 May 2018 14:37:54 GMT

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.

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

js1983 — Thu, 31 May 2018 15:11:29 GMT

Thanks a lot SuriyaKiran,

I will give this a go asap!

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

js1983 — Fri, 01 Jun 2018 05:58:09 GMT

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!

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

SuryaKiran — Fri, 01 Jun 2018 12:50:00 GMT

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;

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

js1983 — Mon, 04 Jun 2018 08:54:33 GMT

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;