How to separate the city and state names into different columns?

[ybz12003]

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

[ChrisNZ · Accepted solution]

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

[ybz12003]

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);