Newbie question on importing raw data with space as delimiter and space between names too

[Sach_sri]

Hi. i have a raw data file that has names of cities in it, followed by some stats. the names of cities may be one-, two-, or three-word long. the words of the city name are separated by a space. the city name and other stats are separated by a double space. so it's like this:

idaho  123  345  789

los angeles  234  567  890

papua new guniea  765  432  109

something like that

i thought i'd found the way around when i wrote a code like this (with dlm set to DOUBLE SPACE):

data stats2;
infile "/folders/myfolders/datasets/sample1.dat" dlm="  " dsd missover;
input city_name :$30. stat1 stat2 stat3;
run;

it won't work. how can i go about it?

[andreas_lds · Accepted solution]

Try dlmstr instead of dlm

[Sach_sri]

Thanks. It worked. I'll make a note of this. I'm sure other answers are bound to work too but with this one I had to change my code so little.

[tomrvincent]

if each row ends with 3 numbers, read the row into a single field, pick out and remove the 3 numbers and what's left is the city name.

[MichaelLarsen]

The solution would be to read the "fixed" values backwards first and then search for the stat1 value in the input record to determine where the last character of city is.

data want;
  length city_name $30 stat1-stat3 8;
  input ;
  stat3 = input(scan(_infile_,-1),8.);
  stat2 = input(scan(_infile_,-2),8.);
  stat1 = input(scan(_infile_,-3),8.);
  city_name = substr(_infile_,1,find(_infile_,scan(_infile_,-3))-1);
  cards;
idaho  123  345  789
los angeles  234  567  890
papua new guniea  765  432  109
;
run;

So your code would look like this:

data stats2;
  infile "/folders/myfolders/datasets/sample1.dat" ;
  length city_name $30 stat1-stat3 8;
  input ;
  stat3 = input(scan(_infile_,-1),8.);
  stat2 = input(scan(_infile_,-2),8.);
  stat1 = input(scan(_infile_,-3),8.);
  city_name = substr(_infile_,1,find(_infile_,scan(_infile_,-3))-1);
run;

[andreas_lds]

To complicated @MichaelLarsen  and @tomrvincent 

data want;
  length city_name $30 stat1-stat3 8;
  infile datalines dlmstr='  ';
  input city_name stat1-stat3;

  datalines;
idaho  123  345  789
los angeles  234  567  890
papua new guniea  765  432  109
;
run;

[ballardw]

It helps to post TEXT data into a code box opened using the forum's {I} icon. The main message windows are likely to reformat text removing extra spaces and so your text example may not be as your actual file.

Also use the code box for Code and Log results for the same reason.

Example: pasting an edited version of your data from my SAS editor into the main window.

idaho 123 345 789

los angeles 234 567 890

papua new guniea 765 432 109

And pasting into a code box. Note the difference. This often means that data steps that read data as pasted into the main window  will not run with your actual data, or that code you paste will not read your inline data.

idaho        123  345  789
los angeles  234  567  890
papua new guniea  765  432  109