Reshape from wide to long

[angeliquec]

Hi,

I'd like to know how can I create this "WANT" dataset from "HAVE". I couldn't find a way how to transpose.

data have;
infile datalines truncover;
input ID VAR_MALAYSIA :$32. VAR_USA :$32.;
datalines;
1 KUALA_LUMPUR CALIFORNIA
2 KELANTAN ALASKA
;
run;

data want;
infile datalines truncover;
input ID COUNTRY :$32. STATE :$32.;
datalines;
1 MALAYSIA KUALA_LUMPUR
1 USA CALIFORNIA
2 MALAYSIA KELANTAN
2 USA ALASKA
;
run;

[RW9 · Accepted solution]

Hi,

data have;
  infile datalines truncover;
  input id var_malaysia :$32. var_usa :$32.;
datalines;
1 KUALA_LUMPUR CALIFORNIA
2 KELANTAN ALASKA
;
run;

proc transpose data=have out=inter;
  by id;
  var var_malaysia var_usa;
run;

data want (keep=id country state);
  set inter;
  length country state $50;
  country=scan(_name_,2,"_");
  state=col1;
run;

[RahulG]

data have;
infile datalines truncover;
input ID VAR_MALAYSIA :$32. VAR_USA :$32.;
datalines;
1 KUALA_LUMPUR CALIFORNIA
2 KELANTAN ALASKA
;
run;
proc transpose data=have out=transposed_have;
by ID;
var VAR_MALAYSIA VAR_USA;
run;

data want(DROP=_NAME_ RENAME=(COL1=STATE));
set transposed_have;
if _NAME_ = 'VAR_MALAYSIA' then COUNTRY='MALAYSIA' ;
if _NAME_ = 'VAR_USA' then COUNTRY='USA' ;
RUN;

You need to work more on the last data step if there are many more countries.