Transpose dataset with or without Proc Transpose

[WorkingMan]

Hi everyone. This is a lil bit tricky for me and I am seeking for advice online. I am currently reading from a xlsx file and then want to transpose the data from wide to long. Here is the trick, have a look at the table structure:

Country    A_Sept17  A_Oct17  B_Sept17  B_Oct17
Denmark  100           99             3                5
Japan       700           350           7                9

And i want it to look like this:

Country   Period   ColA   ColB
Denmark Sept17  100     3
Denmark Oct17    99       5
Japan      Sept17  700     7
Japan      Oct17    350     9

As you all can see, It looks like kinda straightforward transpose. However, when i use PROC Transpose, i do not get the desire result. Here is my code:

proc transpose data=test out=transposed;
by country;

var 
a_sept17
a_oct17
b_factor_sept17
b_factor_oct17;
run;

The result that i got from my code:

Country   Period      Col1
Denmark a_sept17      100
Denmark a_oct17       99
Denmark b_sept17      3 
Denmark b_sept17      5
Japan   a_sept17      700
Japan   a_oct17       350
Japan   b_sept17      7
Japan   b_oct17       9

What could be wrong here?

[Kurt_Bremser]

You need 2 transposes, and a little manipulation in-between:

data have;
input country :$10. a_sept17 a_oct17 b_sept17 b_oct17;
cards;
Denmark 100 99 3 5
Japan 700 350 7 9
;
run;

proc transpose data=have out=trans;
by country;
var a_: b_:;
run;

data trans1;
set trans;
name = scan(_name_,1,'_');
period = scan(_name_,2,'_');
drop _name_;
run;

proc sort data=trans1;
by country period;
run;

proc transpose
  data=trans1
  out=want (drop=_name_)
  prefix=col
;
by country period;
var col1;
id name;
run;

proc print data=want noobs;
run;

Result:

country    period    cola    colb

Denmark    oct17       99      5 
Denmark    sept17     100      3 
Japan      oct17      350      9 
Japan      sept17     700      7 

If you want to have really nice SAS date values, expand the manipulation step:

data trans1;
set trans;
name = scan(_name_,1,'_');
_period = scan(_name_,2,'_');
period = input('01' !! substr(_period,1,3) !! substr(_period,length(_period) - 1),date7.);
drop _name_ _period;
format period yymmn6.;
run;

[singhsahab]

Hello,

here you can go ...

data test;
input Country $ A_Sept17  A_Oct17  B_Sept17  B_Oct17;
datalines;
Denmark 100 99 3 5
Japan 700 350 7 9
;
run;

proc transpose data=test out=want name=name1 prefix=cola;
by country notsorted;
var a_:;
run;

proc transpose data=test out=want1 name=name1 prefix=colb;
by country notsorted;
var b_:;
run;

proc sort data=want;
by country ;
run;
proc sort data=want1;
by country ;
run;

data final (drop=name1);
merge want(in=a) want1(in=b);
by country ;
if a and b;
Period=substr(name1,3);
run;

Thanks,

[RW9]

Assuming those variables appear in month/year order and there is always 2 of them, its easier with arrays:

data want (keep=country month a b);
  set have;
  array x{2} a_:;
  array y{2} b_:;
  do i=1 to 2;
    month=scan(vname(x{i}),2,"_");
    a=x{i};
    b=x{i};
    output;
  end;
run;