Suppose I have a data set as below
data have;
input subject$ day$ arm$ var1 var2;
datalines;
100-01 Day1 left 40 30
100-01 Day1 right 35 25
200-01 Day28 left 45 22
200-01 Day28 right 38 15
;
run;
In this data set each subject
has two row. But I would like to make one row for each subject. The expected data set is
data want;
input subject$ day$ arm_left$ arm_right$ var1_left var1_right var2_left var2_right;
datalines;
100-01 Day1 left right 40 35 30 25
200-01 Day28 left right 45 38 22 15
;
run;
Any help is appreciated.
CodePudding user response:
This gets you almost there, not the arm portion? Do you actually need that variable (arm_left/arm_right) seems redundant.
data have;
input subject$ day$ arm$ var1 var2;
datalines;
100-01 Day1 left 40 30
100-01 Day1 right 35 25
200-01 Day28 left 45 22
200-01 Day28 right 38 15
;
run;
proc transpose data=have out=long;
by subject day arm;
var var1 var2;
run;
proc transpose data=long out=wide delimiter=_;
by subject day;
id _name_ arm;
var col1;
run;
Another way but may not scale:
data have_left;
set have;
by subject day;
where arm='left';
rename arm = arm_left var1=var1_left var2=var2_left;
run;
data have_right;
set have;
where arm='right';
rename arm = arm_right var1=var1_right var2=var2_right;
run;
data want;
merge have_left have_right;
by subject day;
run;