BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
angeliquec
Quartz | Level 8

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

2 REPLIES 2
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Barite | Level 11
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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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