topic Stacking columns into new dataset in SAS Procedures

Stacking columns into new dataset

mthorne — Wed, 19 Sep 2018 18:46:52 GMT

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
;

Re: Stacking columns into new dataset

novinosrin — Wed, 19 Sep 2018 18:59:44 GMT

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

Re: Stacking columns into new dataset

PeterClemmensen — Wed, 19 Sep 2018 19:04:26 GMT

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;

Re: Stacking columns into new dataset

mthorne — Wed, 19 Sep 2018 19:04:35 GMT

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.

Re: Stacking columns into new dataset

novinosrin — Wed, 19 Sep 2018 19:05:54 GMT

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

Re: Stacking columns into new dataset

novinosrin — Wed, 19 Sep 2018 19:17:01 GMT

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;

Re: Stacking columns into new dataset

novinosrin — Wed, 19 Sep 2018 19:24:39 GMT

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;