DATA Step, Macro, Functions and more

Reshape from wide to long

Accepted Solution Solved
Reply
Contributor
Posts: 53
Accepted Solution

Reshape from wide to long

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;

 


Accepted Solutions
Solution
‎10-13-2016 06:47 AM
Super User
Super User
Posts: 7,392

Re: Reshape from wide to long

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;

View solution in original post


All Replies
Solution
‎10-13-2016 06:47 AM
Super User
Super User
Posts: 7,392

Re: Reshape from wide to long

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;
Regular Contributor
Posts: 239

Re: Reshape from wide to long

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 266 views
  • 4 likes
  • 3 in conversation