I want to be able to transpose data entered in multiple columns into a dataset that stacks those columns but still preserves the identifiers for those data and changes column names to represent the new structure. I've tried the macro examples given in other threads, but they don't give the me what I want. I am using SAS 9.4 on a Windows 10 desktop.
This is an example of the initial dataset.
data yield;
input trt rep f1 f2;
cards;
1 1 20 25
1 2 17 15
1 3 30 35
1 4 25 25
2 1 35 33
2 2 30 35
2 3 37 38
2 4 32 37
3 1 42 44
3 2 43 45
3 3 40 45
3 4 43 44
;
This is what I want.
data yield;
input trt rep field yield;
cards;
1 1 1 20
1 2 1 17
1 3 1 30
1 4 1 25
2 1 1 35
2 2 1 30
2 3 1 37
2 4 1 32
3 1 1 42
3 2 1 43
3 3 1 40
3 4 1 43
1 1 2 25
1 2 2 15
1 3 2 35
1 4 2 25
2 1 2 33
2 2 2 35
2 3 2 38
2 4 2 37
3 1 2 44
3 2 2 45
3 3 2 45
3 4 2 44
;
Or as simple as this
data yield;
input trt rep f1 f2;
cards;
1 1 20 25
1 2 17 15
1 3 30 35
1 4 25 25
2 1 35 33
2 2 30 35
2 3 37 38
2 4 32 37
3 1 42 44
3 2 43 45
3 3 40 45
3 4 43 44
;
proc transpose data=yield out=t(rename=(_name_=field)) prefix=yield;
by trt rep;
var f:;
run;
proc sort data=t out=want;
by field;
run;
How many columns f1...fn do you have? is it just the 2 or can vary?
Thank you!
In this example I just used two, but it would depend on the dataset. This is an issue when I do measurements over time on the same experimental unit (plot of ground), so some datasets may have five or six columns of measurements made over time on the same plot.
data yield;
input trt rep f1 f2;
cards;
1 1 20 25
1 2 17 15
1 3 30 35
1 4 25 25
2 1 35 33
2 2 30 35
2 3 37 38
2 4 32 37
3 1 42 44
3 2 43 45
3 3 40 45
3 4 43 44
;
data one(keep=trt rep yield);
set yield;
rename f1=yield;
run;
data two(keep=trt rep yield);
set yield;
rename f2=yield;
run;
data want;
set one two indsname=source;
field=scan(source,2,'.');
run;
data yield;
input trt rep f1 f2;
cards;
1 1 20 25
1 2 17 15
1 3 30 35
1 4 25 25
2 1 35 33
2 2 30 35
2 3 37 38
2 4 32 37
3 1 42 44
3 2 43 45
3 3 40 45
3 4 43 44
;
data temp;
set yield;
array t(*) f1 f2;
do field=1 to dim(t);
yield=t(field);
output;
end;
keep trt rep field yield;
run;
proc sort data=temp out=want;
by field;
run;
Thank you @mthorne, you definitely need an array and i am glad i asked that question. See if the above works
and some hash fun:
data yield;
input trt rep f1 f2;
cards;
1 1 20 25
1 2 17 15
1 3 30 35
1 4 25 25
2 1 35 33
2 2 30 35
2 3 37 38
2 4 32 37
3 1 42 44
3 2 43 45
3 3 40 45
3 4 43 44
;
data _null_;
if _n_=1 then do;
dcl hash h(multidata:'y',ordered:'y');
h.definekey('field');
h.definedata('trt', 'rep', 'field', 'yield');
h.definedone();
end;
set yield end=lr;
array t(*) f:;
do field=1 to dim(t);
yield=t(field);
rc=h.add();
end;
if lr then h.output(dataset:'want');
run;
Or as simple as this
data yield;
input trt rep f1 f2;
cards;
1 1 20 25
1 2 17 15
1 3 30 35
1 4 25 25
2 1 35 33
2 2 30 35
2 3 37 38
2 4 32 37
3 1 42 44
3 2 43 45
3 3 40 45
3 4 43 44
;
proc transpose data=yield out=t(rename=(_name_=field)) prefix=yield;
by trt rep;
var f:;
run;
proc sort data=t out=want;
by field;
run;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.