Solved
Posts: 1,147

# Rows to observations and observation to rows by arrays

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

Thanks,

Thanks,
Jag

Accepted Solutions
Solution
‎11-05-2013 11:55 PM
Posts: 3,167

## Re: Rows to observations and observation to rows by arrays

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

All Replies
Frequent Contributor
Posts: 88

## Re: Rows to observations and observation to rows by arrays

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.

Posts: 1,147

## Re: Rows to observations and observation to rows by arrays

Posted in reply to JerryLeBreton

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,

Thanks,
Jag
Solution
‎11-05-2013 11:55 PM
Posts: 3,167

## Re: Rows to observations and observation to rows by arrays

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
• 3 replies
• 192 views
• 0 likes
• 3 in conversation