read free format external file into sas data sets

[deleted_user]

Does anyone know how can I read character values that contain single embedded blanks and these raw data are not in fixed columns. The & modifier precedes a specified informat can only read data that is end with two or more consecutive blanks. What if the column followed by only one blank? eg. the following raw data includes two colums: city and income and they are seperated by single blank. Can anyone help me figure this out: how can I read this type of raw data into sas data set? Thanks!!

NEW YORK 7,262,700
LOS ANGELES 3,259,340
CHICAGO 3,009,530

[David_SAS]

This is an appropriate question for Technical Support. See
http://support.sas.com/techsup/contact/submit_emits2.html .

-- David Kelley, SAS

[deleted_user]

I am quite eager to see a response for this post, as Dave mentioned from Technical Support.

We had a similar issue, and finally we have requested the source system to provide a file with a defined delimiter, and could resolve it. More interesting if we have city, sur_name, fore_name followed by income..

Best Cheers
Rao

[Olivier]

I'm afraid there would not be any generic answer to that kind of problem. Yet I can suggest some hints to help :

1) DATA ... ;
INFILE ... ;
INPUT ;
After that, a temporary character variable named _INFILE_ is available in the PDV. It consists in the whole line of data.

2) you can then try to "slice" it down into variables using REVERSE, SCAN, PRXMATCH, INDEXW functions.

3) you can test "patterns" in data (such as a number, with or without commas), with PRXMATCH and/or PUT functions.

That would be a painful job, but if you can't get your data source add a real delimiter into your file, I'm afraid you'll have to go through this.

[TobyDunn_hotmail_com]

Seems easy enough:

Data Have ;
Length City $ 20 ;
Infile Cards DLM = ',' ;
Input City Var1 Var2 ;
Income = Input( Scan( City , -1 , ' ' ) , 8. ) ;
City = TranWrd( City , Put( Income , 8. -L ) , '' ) ;
Cards ;
NEW YORK 7,262,700
LOS ANGELES 3,259,340
CHICAGO 3,009,530
;
Run ;