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);
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.