BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Sach_sri
Calcite | Level 5

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?

1 ACCEPTED SOLUTION
6 REPLIES 6
Sach_sri
Calcite | Level 5

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
Rhodochrosite | Level 12
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
SAS Employee

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
Jade | Level 19

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
Super User

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 6 replies
  • 818 views
  • 3 likes
  • 5 in conversation