Transpose many column values in to one column using sas di

[Riteshdell]

Hello All,

I am looking for a solution for below query.

I have 1 row and 12 column source data like below and looking an output as mentioned below.

How can we use in SAS DI studio/ Base sas.

Query;

Data:

Output should be like below:

[art297 · Accepted solution]

Here is one way:

data have;
  input x $ x1-x3 y $ y1-y3 z $ z1-z3;
  cards;
A 10 20 30 B 40 50 60 C 70 80 90
;
data want (keep=col1 col2);
  set have;
  array _col2(*) _numeric_;
  array _col1(*) $ x y z;
  k=-2;
  do i=1 to dim(_col1);
    k+3;
    col1=_col1(i);
    do j=k to (k+2);
      col2=_col2(j);
      output;
    end;
  end;
run;

Art, CEO, AnalystFinder.com

[Riteshdell]

Thanks, @art297, for this solution, 

One more doubt, if I have all columns as character, then do we need to take as char?

[ballardw]

---

@ritesh_dellvostro wrote:

Thanks, @art297, for this solution, 

One more doubt, if I have all columns as character, then do we need to take as char?

---

No. If the variables are the same type you will need to explicitly assign them to an array similar to the way the _col1 array is defined.

And a minor variation on @art297's response that doesn't require you to count and set K as a limit  (assuming all of the numeric group have the same numbers of elements)

data want;
   set have;
   array a x y z;
   array b x1-x3 y1-y3 z1-z3;
   do i=1 to dim(a);
      Col1= a[i];
      do j= 1 to dim(a);
         index = (i-1)*dim(a) +j;
         col2 = b[index];
         output;
      end;
   end;
   keep col1 col3;
;
run;

[Riteshdell]

Hello @art297- Can you tell me code if I have all character column.

[art297]

If all are character:

data have;
  input (x  x1-x3 y  y1-y3 z  z1-z3) ($);
  cards;
A 10 20 30 B 40 50 60 C 70 80 90
;

data want (keep=col1 col2);
  set have;
  array _col2(*) $ x1-x3 y1-y3 z1-z3;
  array _col1(*) $ x y z;
  k=-2;
  do i=1 to dim(_col1);
    k+3;
    col1=_col1(i);
    do j=k to (k+2);
      col2=_col2(j);
      output;
    end;
  end;
run;

[thomp7050]

data have;
length string $ 100;
infile datalines DLM='#';
input string;
datalines;
A 10 20 30 B 20 30 50 C 60 70 80
;
run;

data b(keep=new rename=new=Col1);
length new $8.;
set have;
do i=1 by 1 while(scan(string,i,', ') ^=' ');
new=scan(string,i,', ');
output;
end;
run;

DATA B;
SET B;
RETAIN ID;
IF ANYALPHA(COL1) THEN DO;
ID=COL1;
DELETE;
END;
RUN;

[Ksharp]

It was very easy for IML if they are all character variables.

data have;
  input (x  x1-x3 y  y1-y3 z  z1-z3) ($);
  cards;
A 10 20 30 B 40 50 60 C 70 80 90
;
run;
proc iml;
use have;
read all var _ALL_ into x;
close;
y=shape(x,0,4);
name=repeat(y[,1],1,3);

col1=colvec(name);
col2=colvec(y[,2:4]);

create want var{col1 col2};
append;
close;
quit;