CODEWITHSUNDEEP
sas
STL
STL

Reputation: 293

Retaining a value for all observations in SAS

I have a data set that looks like the following but with multiple patients:

ID   Variable   val    Visit  
A     Height     5     Base
A     Weight     3     Base
A     BMI        1     Base
A     Height     2    Visit 1
A     Weight     4    Visit 1
A     BMI        3    Visit 1

data have;
input id    var $   val visit $;
cards;
A height 5 base
A weight 3 base
A bmi 1 base
A height 2 visit1
A weight 4 visit1
A bmi 3 visit1
;

I would like to create a 3 new columns that retains the base value for all visits: 

 ID   Variable  Value   Visit    Height   Weight   BMI 
 A     Height     5     Base       5        3      1    
 A     Weight     3     Base       5        3      1 
 A     BMI        1     Base       5        3      1 
 A     Height     2    Visit 1     5        3      1  
 A     Weight     4    Visit 1     5        3      1    
 A     BMI        3    Visit 1     5        3      1

I would attempt this by retaining the "base" value but I'm a bit stumped on how to approach it.

Upvotes: 3

Views: 357

Answers (2)

data _null_
data _null_

Reputation: 9109

Adding BASELINE to every obs as you are asking for doesn't seem very useful. How about something like this.

data have;
input id:$1. var $ val visit $;
cards;
A height 5 base
A weight 3 base
A bmi 1 base
A height 2 visit1
A weight 4 visit1
A bmi 3 visit1
;;;;
   run;
proc sort data=have;
   by id var visit;
   run;
proc print;
   run;
data maybe;
   do until(last.var);
      set have;
      by id var;
      if visit eq 'base' then baseline=val;
      if not missing(baseline) then do;
         change = val - baseline;
         end;
      output;
      end;
   run;
proc print;
   run;

enter image description here

Upvotes: 1

SAS2Python
SAS2Python

Reputation: 1296

proc sort data=mydata out=base;
  where visit='base';
  by id;
run;

data base;
  set base;
  by id;

  if first.id then call missing(weight, height, bmi);
  retain height weight bmi;
  if var = 'Height' then height = val;
  else if var = 'Weight' then weight = val;
  else bmi = val;

  if last.id then output;
  keep height weight bmi id;

run;

proc sql;
  create table withbase as
  select * from mydata a full join base b on a.id = b.id;

Upvotes: 1

Related Questions