topic Proc Import -- Multiple Spaces Between Columns in SAS Programming

Proc Import -- Multiple Spaces Between Columns

NKormanik — Fri, 30 Jul 2021 08:51:25 GMT

Data source is nicely formatted on a page. Good amount of white space between columns.

Question is: How to import that into SAS?

proc import
datafile="c:\2\gt\i_20201.txt"
out=sas_1.test
dbms=dlm
replace;

guessingrows = max;
getnames=no;

run;

The five nicely formatted columns get imported as 69 variables, all messed up. Each and every space is being considered a legitimate delimiter.

Thoughts greatly appreciated.

Nicholas Kormanik

Re: Proc Import -- Multiple Spaces Between Columns

andreas_lds — Fri, 30 Jul 2021 09:16:30 GMT

From your description, your files seems to a fixed-width-file, so using a data step is the only way to read it.

Re: Proc Import -- Multiple Spaces Between Columns

Ksharp — Fri, 30 Jul 2021 12:24:36 GMT

Since you have dbms=dlm , you need define a delimiter .

proc import
datafile="c:\2\gt\i_20201.txt"
out=sas_1.test
dbms=dlm
replace;
delimiter=' ';

And better post this TXT file ,so we can test it .

Re: Proc Import -- Multiple Spaces Between Columns

ballardw — Fri, 30 Jul 2021 16:59:27 GMT

Copy the first 5 lines of the text and paste into a text box opened on the forum with the </> icon.

Re: Proc Import -- Multiple Spaces Between Columns

NKormanik — Fri, 30 Jul 2021 23:58:17 GMT

As always, @Ksharp , greatly appreciate your help.

Problem with Proc Import seems to be that it assumes each space represents a separate delimiter.

If you look at a sheet of paper with five columns on it -- separated by appropriate proportional reasonable white space -- one would generally expect SAS to 'bring in' the five columns, no issue.

Proc Import doesn't apparently see it that way.

Re: Proc Import -- Multiple Spaces Between Columns

NKormanik — Sat, 31 Jul 2021 00:08:47 GMT

Not exactly 'fixed width', @andreas_lds , not precisely. Bit of zig and zag to the columns. But just looking at the page there is no doubt that the data is arranged in columns. Five fairly nicely displayed columns.

So, as you say, one apparently has to turn to the data step, which worked in this present case, no problem.

data sas_1.test ;
infile "c:\2\gt\i_21503a.txt"
;

format Indicator $30. ;
format Parameter 12.2 ;
format Number 12.0 ;
format Success 12.2 ;
format Fail 12.2 ;

Input
Indicator $
Parameter
Number
Success
Fail
;

run;

Re: Proc Import -- Multiple Spaces Between Columns

NKormanik — Sat, 31 Jul 2021 00:19:09 GMT

Very standard stuff, @ballardw . But down a ways there is some zig-zag to the columns. I've purposely added an extra space here and there to show....

i_21503a    2.64455     74    0.4189     0.5811
i_21503a   2.64455     74    0.4189     0.5811
i_21503a   3.02739      2    0.0000     1.0000
i_21503a   3.02739     2    0.0000     1.0000
i_21503a   3.02739       2    0.0000     1.0000

Re: Proc Import -- Multiple Spaces Between Columns

Tom — Sat, 31 Jul 2021 03:53:56 GMT

@NKormanik wrote:

Very standard stuff, @ballardw . But down a ways there is some zig-zag to the columns. I've purposely added an extra space here and there to show....
i_21503a    2.64455     74    0.4189     0.5811
i_21503a   2.64455     74    0.4189     0.5811
i_21503a   3.02739      2    0.0000     1.0000
i_21503a   3.02739     2    0.0000     1.0000
i_21503a   3.02739       2    0.0000     1.0000

So that is NOT a delimited file. So why did you try to read it with PROC IMPORT?

As long as each line has five values a simple NORMAL data step will read easily.

data want;
  infile 'myfile.txt' truncover ;
  input var1 :$10. var2-var5 ;
run;

Re: Proc Import -- Multiple Spaces Between Columns

NKormanik — Sat, 31 Jul 2021 06:50:28 GMT

@Tom , seemed reasonable that Proc Import would be the go-to way to get data in. Didn't know that it was mainly for delimited data -- and for only single delimiter characters separating values.

Just to add a bit of clarity for posterity, further questions:

data want;
  infile 'myfile.txt' truncover ;
  input var1 :$10. var2-var5 ;
run;

What's "truncover"?

Why the colon?

Re: Proc Import -- Multiple Spaces Between Columns

Tom — Sat, 31 Jul 2021 15:56:36 GMT

The go-to way to read data into SAS is the INPUT statement of the DATA step. PROC IMPORT is for reading files in structured formats, like SPSS or DBF files.

The TRUNCOVER option on the INFILE statement will prevent SAS from going to the next line if the current line does not have enough data to satisfy the INPUT statement. The default behavior is to flow to the next line. That way if a single observation does not fit on one line you can continue the data on the next line in the input file.

The : modifier tells the INPUT statement to use LIST MODE rather than FORMATTED MODE even though there is an in-line informat in the INPUT statement. Without it INPUT would read exactly the number of characters the informat specified even if that reads past the delimiter and into the next word on the line.

There is not need to use PROC IMPORT for reading a delimited text file either. All PROC IMPORT does when you ask it to read a delimited file is do an analysis of what it sees in the file and generates a data step to read. You can write a much better data step than it does and then you will have full control over how the variables are named, typed, formatted, labeled. etc. Plus you won't be fooled into defining fields like ZIPCODE, PHONE_NUMBER or ID as numbers instead of characters because they only contain digits.