Transposing wide table into long format in SAS

Iden Crisler 0
Viewed 38

I have a dataset that looks roughly like this:

data wide; 
  input id age gender nationality a_active b_active a_eligible b_eligible; 
cards; 
1 33 M X 0 1 1 0
; 
run;

Desired output:

id age gender nationality active_label active_value eligible_label eligible_value
1 33 M X a 0 a 1
1 33 M X b 1 b 0

I tried using proc transpose but I can't seem to figure out how to have multiple labels. I can do this with one label, not sure if that's the right way:

proc transpose data=wide out=long pefix=active_label;
    by id age gender nationality;
    var a_active b_active;
run;
sas
3 Answers

You can achieve your required result with 2 proc transpose steps. The labels can be split out in a data step in between.

data wide; 
  input id age gender $ nationality $ a_active b_active a_eligible b_eligible; 
cards; 
1 33 M X 0 1 1 0
; 
run;

* First transpose into a long format. The name column automatically will be
    called _NAME_ and the value column gets named COL1;
proc transpose data=wide out=long ;
    by id age gender nationality;
    var a_: b_:;
run;   

* now separate the _NAME_ variable into label (A,B) and varlabel (active,eligible);
data long;
    set long;
    label=scan(_name_,1,'_');
    varlabel=scan(_name_,2,'_');
run;

* now transpose into a wider format, but keeping the label as a single column;
proc transpose data=long out=want (drop=_name_);
    by id age gender nationality label;
    id varlabel;
    var col1;
run;
shaun_m0

I suppose you could do it in multiple steps and then merge the results together:

/* Transpose active */
proc transpose data=wide name=active_label out=long1(rename=(COL1 = active_value) );
    by id age gender nationality;
    var a_active b_active;
run;  
 
/* Transpose eligible */
proc transpose data=wide name=eligible_label out=long2(rename=(COL1 = eligible_value) );
    by id age gender nationality;
    var a_eligible b_eligible;
run;  

/* Merge both results together */
data want;
    merge long1 long2;
    by id age gender nationality;

    /* The label is the first word in each label column */
    active_label   = scan(active_label, 1, '_');
    eligible_label = scan(eligible_label, 1, '_');
run;
Stu Sztukowski0

This is one of those cases where a data step may be easier. Especially if you need to extend it to more variables.

data wide;
    input id age gender $ nationality $ a_active b_active a_eligible b_eligible;
    cards;
1 33 M X 0 1 1 0
;
run;

data long;
    set wide;
    array _active (*) a_active b_active;
    array _eligible(*) a_eligible b_eligible;

    do i=1 to dim(_active);
        label=scan(vname(_active(i)), 1, "_");
        active_value=_active(i);
        eligible_value=_eligible(i);
        output;
    end;
    drop a_: b_: i;
run;

Or another transpose option that's a touch more dynamic than either of the solutions so far:

data wide;
    input id age gender $ nationality $ a_active b_active a_eligible b_eligible;
    cards;
1 33 M X 0 1 1 0
;
run;

proc transpose data=wide  out=long1(rename=(COL1 = value) );
    by id age gender nationality;
    var a_active b_active a_eligible b_eligible;
run;  

data long2;
set long1;
label = scan(_name_, 1, "_");
var_name = scan(_name_, 2, "_");
run;

proc sort data=long2;
by id age gender nationality label;
run;

proc transpose data=long2 out=wide1 (drop = _name_) ;
by id age gender nationality label;
id var_name;
var value;
run;
Reeza0
Related