Combining columns in SAS

Question

I just started using SAS and I'm trying to combine columns.

I've got table mainData

A1 A2 A3 A4
1  4  7  10
2  5  8  11
3  6  9  12

I want to create a new table rearrangedData

Type Value
A1    1
A1    2
A1    3
A2    4
A2    5
A2    6
A3    7
A3    8
A3    9
A4    10
A4    11
A4    12

There must be a simple solution to this I just can't figure this out. I'm thinking of writing do loop, but what if I don't know size of a table or amount of lines in a specific column. I can't figure how I would get such information in SAS.

Answer 1

This somewhat unusual transformation can be done via a transpose and some array logic:

data have;
input A1 A2 A3 A4;
cards;
1  4  7  10
2  5  8  11
3  6  9  12
;
run;

proc transpose data = have out = tr name=type prefix = r;
run;

data want;
 set tr;
 array r{*} r:;
 do i = 1 to dim(r);
    value = r[i];
    output;
 end;
 drop i r:;
run;

Also, this preserves the original order without requiring a sort.

Answer 2

Make a dummy variable, then transpose data.

data have;
   set have;
   id=_n_;
run;

proc transpose data=have out=temp;
by id;
var A1-A4;
run;

proc sort data=temp out=want(rename=(_name_=type col1=value) drop=id);
by _name_;
run;

Answer 3

If you want to preserve the original order then you could use the POINT= option on the SET statement to loop over the data set once per variable (column).

So this data set will read the first observations just to get the variables defined. Then define the array VALUES so that we can use DIM(VALUES) to know how many columns. Then it uses the POINT= and NOBS= options on the SET statement to control the other loop. It uses the VNAME() function to find the name of the current variable in the array.

data want ;
   set have ;
   array values _numeric_;
   do col=1 to dim(values);
     length type $32 value 8;
     type=vname(values(col));
     do row=1 to nobs ;
        set have point=row nobs=nobs ;
        value=values(col);
        output;
        keep type value;
     end;
   end;
   stop;
run;