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?
Try dlmstr instead of dlm
Try dlmstr instead of dlm
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.
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;
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.