reshape table by two variables

[sasecn]

Hello:

I have a table "have" which can be created by the code attached. I would like to reshape it to the table "want" (see eg in the code). Basicly, the table "have" contains the counts of types of variable1 and variable2. It can be thought as types of sales-person, non_sales-person, both types for var1. For var2, it could be locations of "a" or "b". I have tried proc transpose. But I failed to do so since not sure how to handle the varialbe names.

data have;
input year num_s_a num_s_b num_ns_a num_ns_b num_both_a num_both_b;
cards;
2010 1 2 3 4 5 6
2011 7 8 9 10 11 12
;
run;

data want;
input year v1_type $ v2_type $ count;
cards;
2010 s a 1
2010 s b 2
2010 ns a 3
2010 ns b 4
2010 both a 5
2010 both b 6
2011 s a 7
2011 s b 8
2011 ns a 9
2011 ns b 10
2011 both a 11
2011 both b 12
;
run;

[andreas_lds · Accepted solution]

Try

data want;
   set have;
   length 
      v1_type v2_type $ 4 count 8
      _varName $ 32
   ;
   
   array vars num_:;
   
   do _i = 1 to dim(vars);
      _varName = vname(vars[_i]);
      v1_type = scan(_varName, 2, '_');
      v2_type = scan(_varName, 3, '_');
      count = vars[_i];
      output;
   end;
   
   drop num_: _:;
run;

[sasecn]

Thanks, that works. I thought i can use proc transpose, but the data step works!

[singhsahab]

@sasecn you can try below code:

data have;
input year num_s_a num_s_b num_ns_a num_ns_b num_both_a num_both_b;
cards;
2010 1 2 3 4 5 6
2011 7 8 9 10 11 12
;
run;

proc transpose data=have out=have1(rename=(col1=count)) name=varname;
by year notsorted;
var num:;
run;

data want;
set have1;
v1_type=scan(varname,2,'_');
v2_type=scan(varname,3,'_');
drop varname;
run;

[sasecn]

Thank you very much! That's what i was looking for. Wish i can have an option to mark yours as solution as well. The system only allows me to mark one.

Thanks again!