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;
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;
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;
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.