topic Re: Newbie question on importing raw data with space as delimiter and space between names too in New SAS User

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

Sach_sri — Thu, 28 Mar 2019 12:01:01 GMT

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?

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

andreas_lds — Thu, 28 Mar 2019 12:16:08 GMT

Try dlmstr instead of dlm

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

tomrvincent — Thu, 28 Mar 2019 12:23:44 GMT

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.

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

MichaelLarsen — Thu, 28 Mar 2019 12:24:40 GMT

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;

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

andreas_lds — Thu, 28 Mar 2019 12:27:57 GMT

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;

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

ballardw — Thu, 28 Mar 2019 15:58:34 GMT

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

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

Sach_sri — Thu, 28 Mar 2019 16:39:01 GMT

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.