BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
js1983
Calcite | Level 5

 

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
SuryaKiran
Meteorite | Level 14

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;

 

 

Thanks,
Suryakiran

View solution in original post

5 REPLIES 5
SuryaKiran
Meteorite | Level 14

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.

Thanks,
Suryakiran
js1983
Calcite | Level 5

Thanks a lot SuriyaKiran, 

 

I will give this a go asap!

 

js1983
Calcite | Level 5

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
Meteorite | Level 14

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;

 

 

Thanks,
Suryakiran
js1983
Calcite | Level 5

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 5273 views
  • 0 likes
  • 2 in conversation