Hello, all:
I have the following variable "organization". I would like to extract the city and state into different variables. How to do it? Please help. Thanks.
YT
Organization
Abounding Prosperity (Dallas, TX)
Asian Pacific Islander Coalition on HIV/AIDS (APICHA) (New York, NY)
My Brother’s Keeper (Ridgeland, MS)
Organization Cities States
Abounding Prosperity Dallas TX
Asian Pacific Islander Coalition on HIV/AIDS (APICHA) New York NY
My Brother’s Keeper Ridgeland MS
Like
this?
data HAVE;
infile cards pad;
input ORGANISATION $80.;
cards;
Abounding Prosperity (Dallas, TX)
Asian Pacific Islander Coalition on HIV/AIDS (APICHA) (New York, NY)
My Brother’s Keeper (Ridgeland, MS)
run;
data WANT;
set HAVE;
POS = find(ORGANISATION,'(',-99);
CITY = scan(substr(ORGANISATION,POS+1),1,',');
STATE = scan(substr(ORGANISATION,POS+1),2,',)');
ORGANISATION= substr(ORGANISATION,1,POS-1);
putlog STATE= CITY= ORGANISATION=;
run;
STATE=TX CITY=Dallas ORGANISATION=Abounding Prosperity
STATE=NY CITY=New York ORGANISATION=Asian Pacific Islander Coalition on HIV/AIDS (APICHA)
STATE=MS CITY=Ridgeland ORGANISATION=My Brother’s Keeper
Like
this?
data HAVE;
infile cards pad;
input ORGANISATION $80.;
cards;
Abounding Prosperity (Dallas, TX)
Asian Pacific Islander Coalition on HIV/AIDS (APICHA) (New York, NY)
My Brother’s Keeper (Ridgeland, MS)
run;
data WANT;
set HAVE;
POS = find(ORGANISATION,'(',-99);
CITY = scan(substr(ORGANISATION,POS+1),1,',');
STATE = scan(substr(ORGANISATION,POS+1),2,',)');
ORGANISATION= substr(ORGANISATION,1,POS-1);
putlog STATE= CITY= ORGANISATION=;
run;
STATE=TX CITY=Dallas ORGANISATION=Abounding Prosperity
STATE=NY CITY=New York ORGANISATION=Asian Pacific Islander Coalition on HIV/AIDS (APICHA)
STATE=MS CITY=Ridgeland ORGANISATION=My Brother’s Keeper
Thanks, it works.
But I think it might be changed the postion to -80 instead of -99, cause the previous input variable "ORGANIZATION" is 80 charaterize.
POS = find(ORGANISATION,'(',-80);
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.