Rows to observations and observation to rows by arrays

Accepted Solution Solved
Reply
Trusted Advisor
Posts: 1,137
Accepted Solution

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

Appreciate your responses.

Thanks,

Jagadish

Thanks,
Jag

Accepted Solutions
Solution
‎11-05-2013 11:55 PM
Respected Advisor
Posts: 3,156

Re: Rows to observations and observation to rows by arrays

Posted in reply to Jagadishkatam

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

View solution in original post


All Replies
Frequent Contributor
Posts: 85

Re: Rows to observations and observation to rows by arrays

Posted in reply to Jagadishkatam

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.

Trusted Advisor
Posts: 1,137

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,

jagadish

Thanks,
Jag
Solution
‎11-05-2013 11:55 PM
Respected Advisor
Posts: 3,156

Re: Rows to observations and observation to rows by arrays

Posted in reply to Jagadishkatam

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
  • 180 views
  • 0 likes
  • 3 in conversation