Stacking columns into new dataset

[mthorne]

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
;

[novinosrin]

How many columns f1...fn do you have? is it just the 2 or can vary?

[mthorne]

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.

[PeterClemmensen]

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;

[novinosrin]

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 

[novinosrin]

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;

[novinosrin · Accepted solution]

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;