DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Super Contributor
Posts: 319
Accepted Solution

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

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

 

 


Accepted Solutions
Solution
‎07-06-2016 03:47 PM
PROC Star
Posts: 1,760

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

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

View solution in original post


All Replies
Solution
‎07-06-2016 03:47 PM
PROC Star
Posts: 1,760

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

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
Super Contributor
Posts: 319

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

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 259 views
  • 1 like
  • 2 in conversation