topic Re: Rows to observations and observation to rows by arrays in SAS Programming

Rows to observations and observation to rows by arrays

Jagadishkatam — Wed, 06 Nov 2013 01:59:12 GMT

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

Re: Rows to observations and observation to rows by arrays

JerryLeBreton — Wed, 06 Nov 2013 02:27:00 GMT

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.

Re: Rows to observations and observation to rows by arrays

Jagadishkatam — Wed, 06 Nov 2013 02:35:30 GMT

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

Re: Rows to observations and observation to rows by arrays

Haikuo — Wed, 06 Nov 2013 04:55:02 GMT

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;

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