Importing Ascii file with fixed column but inconsistent delimiters

[MelissaMuharam]

Dear All,

I am trying to establish a macro to import ascii files that have fixed columns but they have inconsistent number of a space delimiter. I am far to any success. My data looks like this;

291.91 7.01 49.92
293.28 8.59 55.92
294.67 8.58 62.58
296.08 10.98 68.24
297.49 11.13 73.68
298.92 10.98 80.24
300.37 14.35 101.75
301.82 22.59 142.68
303.29 28.95 195.66
304.77 32.23 251.02
1099.91 -510.07 850.12
1102.76 511.29 766.93
1105.66 0.00 348.10
1108.60 625.03 1250.06
1111.58 541.22 -631.42
1114.60 -854.57 854.57
1117.67 774.13 290.30
1120.78 302.89 201.93
1123.94 -209.54 314.31
1127.15 -211.53 317.29

or

1102.76 119981.68 234509.66
1105.66 126010.92 245059.90
1108.60 115005.43 247154.52
1111.58 126284.38 228033.50
1114.60 118500.25 238519.74
1117.67 116893.37 233786.74
1120.78 121965.48 247161.83
1123.94 112313.59 238037.76
1127.15 119300.86 216602.97
1130.40 116193.22 254344.21

The code that I have been trying are;

1) INFILE

%macro Import (Dir=, WorkFile=);
*PROC PRINTTO log = '';
PROC SQL noprint;
select file into :dfile
separated by ' '
from Dir_Ref;
RUN;
%let k=1;
%let data = %scan(&dfile, &k);
*%let ext = .txt;
%do %while("&data" NE "");
filename &data "&Dir&data";
Data &data_data;
INFILE &data firstobs=14;
INPUT Var1 1-7 Var2 10-18 Var3 20-28;
RUN;
%let k = %eval(&k + 1);
%let data = %scan(&dfile, &k);
%end;
;
RUN;
PROC PRINTTO; RUN;

2) PROC IMPORT

%macro Import (Dir=, WorkFile=);
*PROC PRINTTO log = '';
PROC SQL noprint;
select file into :dfile
separated by ' '
from Dir_Ref;
RUN;
%let k=1;
%let data = %scan(&dfile, &k);
*%let ext = .txt;
%do %while("&data" NE "");
PROC IMPORT
datafile ="&Dir&data"
out= &data
dbms= dlm
replace;
delimiter=' ' ;
getnames=NO; GUESSINGROWS=14 to 526
RUN;
%let k = %eval(&k + 1);
%let data = %scan(&dfile, &k);
%end;
;
RUN;
PROC PRINTTO; RUN;
%mend Import;

Your assistances are deeply appreciated. Many thanks.

[SASJedi]

Melissa,
It appears you actually have space delimited text file, not a fixed-column file. Change the data step to use LIST input, and it should go well.

In the INFILE section, change the data step from:

Data &data_data;
INFILE &data firstobs=14;
INPUT Var1 1-7 Var2 10-18 Var3 20-28;
RUN;

to:

Data &data_data;
INFILE &data firstobs=14;
INPUT Var1 Var2 Var3;
RUN;

[MelissaMuharam]

Dear SASJedi,

It seems that when I copied and pasted the data, it didn't do any justice to the original data file. It is actually looks like this,

291.91 -0.04 1.00
(3 spaces between column 1 and 2, and 5 spaces between column 2 and 3)

293.28 -0.07 0.09
(3 spaces between column 1 and 2, and 5 spaces between column 2 and 3)

294.67 0.13 0.15
(3 spaces between column 1 and 2, and 6 spaces between column 2 and 3)

296.08 0.00 0.23
(3 spaces between column 1 and 2, and 6 spaces between column 2 and 3)

1037.67 336.14 -126.05
(2 spaces between column 1 and 2, and 4 spaces between column 2 and 3)

1097.09 -1134.11 243.02
(2 spaces between column 1 and 2, and 2 spaces between column 2 and 3)

However, the column number seems fixed for each column (1 to 7, 10 to 18 and 20 - 28). I also have tried your suggestion, but it didn't work as well. Your assistance is really appreciated.

[SASJedi]

Melissa,

The following test code executes without issue on SAS9.2 under Windows 7:

*Begin code ****************************************************;
/* Create a text file to experiment with */
filename textfile 'c:\temp\test.txt';
data _null_;
file textfile;
if _n_=1 then do i=1 to 13;
PUT "Do not read this";
end;
input;
put _infile_;
datalines;
291.91 -0.04 1.00
293.28 -0.07 0.09
294.67 0.13 0.15
296.08 0.00 0.23
1037.67 336.14 -126.05
1097.09 -1134.11 243.02
;
run;

Data test;
INFILE textfile firstobs=14;
INPUT Var1 Var2 Var3;
RUN;
proc print; run;
*End code ****************************************************;
OUTPUT:
Obs Var1 Var2 Var3
1 291.91 -0.04 1.00
2 293.28 -0.07 0.09
3 294.67 0.13 0.15
4 296.08 0.00 0.23
5 1037.67 336.14 -126.05
6 1097.09 -1134.11 243.02

The Data _NULL_ step correctly re-created the text just as you described it to me, (I checked it out in UltraEdit) and the second Data step read it in without a hitch using list input.

Please specify exactly what problem you are experiencing. It would also be useful to have a small sample of the exact text that is NOT reading in correctly, along with an example of how it looks in SAS, so we can further diagnose the problem.

Stay SASy!
SASJedi