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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.