BookmarkSubscribeRSS Feed
MelissaMuharam
Calcite | Level 5
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.
3 REPLIES 3
SASJedi
SAS Super FREQ
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;
Check out my Jedi SAS Tricks for SAS Users
MelissaMuharam
Calcite | Level 5
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
SAS Super FREQ
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
Check out my Jedi SAS Tricks for SAS Users

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1389 views
  • 0 likes
  • 2 in conversation