How to use SAS to split a string into two variables

Question

1. I have a dataset as below:

   country
   United States, Seattle
   United Kingdom, London
   

How can I split country into a data in SAS like:

    country                  city
    United States           Seattle
    United Kingdom          London

Answer 1

Use function SCAN() with comma as separator.

data test;
  set test;
  city=scan(country,2,',');
  country=scan(country,1,',');
run;

Answer 2

Another option, INFILE magic (google the term for papers on the topic); useful for parsing many variables from one string and/or dealing with quoted fields and such that would be more work with scan.

filename tempfile "c:\temp\test.txt";


data have;
input @1 country $50.;
datalines;
United States, Seattle
United Kingdom, London
;;;;
run;

data want;
set have;
infile tempfile dlm=',' dsd;
input @1 @@;
_infile_=country;
format newcountry city $50.;
input newcountry $ city $ @@;
run;

tempfile can be any file (or one you create on the fly with any character in it to avoid premature EOF).

Answer 3

Response to:

data test;
  set test;
  city=scan(country,2,',');
  country=scan(country,1,',');
run;

What if I want to split the last comma in the string only, keeping 7410 City?

Example: "Junior 18, Plays Piano, 7410 City