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);
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.