topic Re: transpose row data into multi columns in SAS Procedures

transpose row data into multi columns

deleted_user — Mon, 22 Dec 2008 19:37:10 GMT

Below are the contents of my dataset with 4 observations and 4 columns(a,b,c,d) with values x, y, z, x in each observation.
a b c d
1 x x x x
2 y y y y
3 z z z z
4 x x x x

Now, I need to transform this into below structure:

a1 b1 c1 d1 a2 b2 c2 d2 a3 b3 c3 d3 a4 b4 c4 d4
== == == == == == == == == == == == == == == ==
x x x x y y y y z z z z x x x x

In the other sense, all the row data needs to be transformed into columns (appending each column name with the observation a1, b1, a2, b2 etc...)

How I can achieve this, any easy way to accomplish this?

Re: transpose row data into multi columns

GertNissen — Mon, 22 Dec 2008 21:46:51 GMT

Well this is one way of doing it[pre]
data input;
length a b c d $1;
input a b c d;
datalines;
x x x x
y y y y
z z z z
x x x x
;
run;[/pre][pre]
* find variable names, number of varibales and number of obs *;
proc sql NOPRINT;
select name into :varnames SEPARATED by ','
from sashelp.vcolumn
where libname = 'WORK' and memname = 'INPUT';

select nobs into :nobs
from sashelp.vtable
where libname = 'WORK' and memname = 'INPUT';

select nvar into :nvar
from sashelp.vtable
where libname = 'WORK' and memname = 'INPUT';
quit;;[/pre][pre]
* create list with new variables *;
data _null_;
length _x_ $2000;
do vars = 1 to &nvar.;
do obs = 1 to &nobs.;
_x_ = catx(' ',_x_,cats(scan("&varnames.",obs,','),vars));
end;
end;
call symput('varlist',strip(_x_));
run;;[/pre][pre]
* transpose input data into one record *;
data transposed(keep=&varlist.);
retain &varlist.;
array vars{&nvar.,&nobs.} $1. &varlist.;
set input end=last;
do i = 1 to &nvar.;
x=scan("&varnames",i,',');
y=vvaluex(x);
vars{_n_,i}=vvaluex(x);
end;
if last then do; output; end;
run;
[/pre]

Re: transpose row data into multi columns

deleted_user — Tue, 23 Dec 2008 03:46:53 GMT

use the following to cover both variable types.
/*
data input;
length a b $1 c d $2;
input a b c d gm;
datalines;
x x x x 1
y y y y 0
z z z z 3
x x x x 4
;
run;
data _null_;
call execute('data transposed;');
length name $ 200 type $ 1 length 8;
do _n_=1 by 1 until (last);
set input end=last;
do until(name eq '');
call vnext(name, type, length);
if name not in ('', 'name', 'type', 'length', '_N_', '_ERROR_', 'last') then
if type eq 'N' then
call execute(cats(name, put(_n_, best.)) || '=' || vvaluex(name) || ';');
else
call execute(cats(name, put(_n_, best.)) || '="' || vvaluex(name) || '";');
end;
end;
call execute('run;');
stop;
run;
*/