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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.