DATA Step, Macro, Functions and more

Splitting a city and zip code into 2 variables

Reply
New Contributor
Posts: 2

Splitting a city and zip code into 2 variables

[ Edited ]

Class assignment that I am stuck on.  Sorry if this is simple.  I need to split the last variable in the data City, state, zip code into 2 variables city and zip code.  In the code I am calling the variables city and zipcode.  I can not figure out how to have sas read the city separate from the zip code.  I have tried the input statement that is blocked out with the /*.......*/  (this was done without the format statement and the other input statement shown below) but the city length is not the same distance.  I tried a dlm comma statement but not all variables have commas separating,

 

I tried the copied information statement to no avail.

 

 

Data agentd;
informat customer $1. date1 yymmdd10. date2 yymmdd10. city $30. zipcode 6.;
/*input customer $ date1 : yymmdd10. date2 : yymmdd10. city $14. zipcode;*/
input customer date1 date2 city zipcode;
format date1 date2 mmddyy8.;
datalines;
1.  2014-03-11.  2014-05-31.  Bremen, KS, 66412
2.  2015-08-06   2015-09-02.  Little River, KS, 67457

 

Id love any suggestions from the masses of experts

 

Ben

Super User
Posts: 3,763

Re: Splitting a city and zip code into 2 variables

[ Edited ]
Posted in reply to bennessmith
Data agentd;
informat customer $2. date1 yymmdd10. date2 yymmdd10. city $30. zipcode 6. city_zipcode $30.;
input customer $ date1 date2 city_zipcode & $30.;
city = scan(city_zipcode, 1, ',');
zipcode = input(scan(city_zipcode, -1, ' '), 5.);
format date1 date2 mmddyy8.;
datalines;
1. 2014-03-11 2014-05-31 Bremen, KS, 66412
2. 2015-08-06 2015-09-02 Little River, KS, 67457
;
run;
New Contributor
Posts: 2

Re: Splitting a city and zip code into 2 variables

Thank you so very much.  so awesome to see the various ways to do this.

 

If I see that I need to add a variable to this dataset with the same value  how would I do that?

 

example:

 

For each customer I want there to be a variable named AGENT.   Each customer of this data set will have the value for AGENT of D.

 

I know how to add a computed value but not how to add this added consistent value.  

 

 

Finding this group is incredible

 

Thanks again

Contributor
Posts: 56

Re: Splitting a city and zip code into 2 variables

Posted in reply to bennessmith
Data agentd;
informat customer $1. date1 yymmdd10. date2 yymmdd10.;
input customer date1 date2 city_ipcode $30.;
city= substr(city_ipcode,1,length(city_ipcode)-length(scan(city_ipcode,-1,',')));
zipcode=input(scan(city_ipcode,-1,','),6.);
format date1 date2 mmddyy8.;
datalines;
1. 2014-03-11   2014-05-31  Bremen, KS, 66412
2. 2015-08-06   2015-09-02  Little River, KS, 67457
;
run;
Super User
Posts: 10,610

Re: Splitting a city and zip code into 2 variables

Posted in reply to bennessmith
Data agentd;
informat customer $2. date1 yymmdd10. date2 yymmdd10. city $30. zipcode 6. city_zipcode $30.;
input customer $ date1 date2 city_zipcode & $30.;
city = prxchange('s/,\s*\d+$//',1,strip(city_zipcode));
zipcode = input(scan(city_zipcode, -1, ' '), 5.);
format date1 date2 mmddyy8.;
datalines;
1. 2014-03-11 2014-05-31 Bremen, KS, 66412
2. 2015-08-06 2015-09-02 Little River, KS, 67457
;
run;
Ask a Question
Discussion stats
  • 4 replies
  • 132 views
  • 0 likes
  • 4 in conversation