Home > Software design >  How to transfer long data to wide format data in sas
How to transfer long data to wide format data in sas

Time:08-21

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