Dear All,
Could anyone please let me know, how we can convert the below data (this is a sample data)
a b c
1 2 3
4 5 6
7 8 9
desired output
a b c
1 4 7
2 5 8
3 6 9
Appreciate your responses.
Thanks,
Jagadish
If you have big enough memory to hold the whole table, then you can try this:
data have;
input a b c;
cards;
1 2 3
4 5 6
7 8 9
;
run;
proc sql NOPRINT;
select nobs into :nobs TRIMMED from dictionary.tables where LIBNAME='WORK' AND MEMNAME='HAVE';
SELECT NVAR INTO :NVAR TRIMMED FROM DICTIONARY.TABLES WHERE LIBNAME='WORK' AND MEMNAME='HAVE';
QUIT;
data want;
set have end=last nobs=nobs;
array t1 a b c;
array t2(&nobs,&nvar) _temporary_;
do over t1;
t2(_n_,_i_)=t1;
end;
if last then do;
do _n_=1 to nobs;
do over t1;
t1=t2(_i_,_n_);
end;
output;
end;
end;
run;
Note: if your nobs not equal to your nvar, then the code needs to be tweaked a bit, basically you would need to define another array for the new vars.
Haikuo
Do you really want to use arrays?
Using Proc Transpose...
data have;
input a b c;
cards;
1 2 3
4 5 6
7 8 9
;
run;
proc transpose data=have out=want;
var a b c;
run;
How best to name/rename the transposed columns will depend on your real data.
Yes Jerry, thanks for your response.
i want to achieve this by arrays.
Because i tried with transpose and got the desired output, even without the use of var statement.
proc transpose data=have out=trans;
run;
Thanks,
jagadish
If you have big enough memory to hold the whole table, then you can try this:
data have;
input a b c;
cards;
1 2 3
4 5 6
7 8 9
;
run;
proc sql NOPRINT;
select nobs into :nobs TRIMMED from dictionary.tables where LIBNAME='WORK' AND MEMNAME='HAVE';
SELECT NVAR INTO :NVAR TRIMMED FROM DICTIONARY.TABLES WHERE LIBNAME='WORK' AND MEMNAME='HAVE';
QUIT;
data want;
set have end=last nobs=nobs;
array t1 a b c;
array t2(&nobs,&nvar) _temporary_;
do over t1;
t2(_n_,_i_)=t1;
end;
if last then do;
do _n_=1 to nobs;
do over t1;
t1=t2(_i_,_n_);
end;
output;
end;
end;
run;
Note: if your nobs not equal to your nvar, then the code needs to be tweaked a bit, basically you would need to define another array for the new vars.
Haikuo
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.