I have a dataset as below:
countryUnited States, SeattleUnited Kingdom, London
How can I split country into a data in SAS like:
country cityUnited States SeattleUnited Kingdom LondonBest Answer
Use function SCAN() with comma as separator.
data test;set test;city=scan(country,2,',');country=scan(country,1,',');run;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, SeattleUnited 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).
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