DATA Step, Macro, Functions and more

Importing Ascii file with fixed column but inconsistent delimiters

Reply
New Contributor
Posts: 2

Importing Ascii file with fixed column but inconsistent delimiters

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.
SAS Employee
Posts: 104

Re: Importing Ascii file with fixed column but inconsistent delimiters

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;
New Contributor
Posts: 2

Re: Importing Ascii file with fixed column but inconsistent delimiters

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.
SAS Employee
Posts: 104

Re: Importing Ascii file with fixed column but inconsistent delimiters

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
Ask a Question
Discussion stats
  • 3 replies
  • 377 views
  • 0 likes
  • 2 in conversation