topic How to separate the city and state names into different columns? in SAS Programming

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

ybz12003 — Sat, 02 Jul 2016 20:09:00 GMT

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.

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

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

ChrisNZ — Wed, 06 Jul 2016 02:18:30 GMT

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

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

ybz12003 — Wed, 06 Jul 2016 19:46:53 GMT

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