Transposing wide table into long format in SAS

Question

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;   

Answer 1

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;