topic Re: How can I import text delimited file with a large amount of columns and avoid truncation? in SAS Procedures

How can I import text delimited file with a large amount of columns and avoid truncation?

ymzayek — Tue, 18 Nov 2014 21:50:48 GMT

Hi,

I'm trying to import a comma delimited txt file into SAS and this is the code I'm using:

proc import datafile='U:\DICA raw data.txt' dbms=dlm out=DICA_new replace;

delimiter=","; getnames=no; datarow=1 ;

I'm ending up with an output of the first 1171 variables, but my data has around 1800. How do I keep it from cutting off the rest of the columns?

example of what my data looks like:

"3","","1","","2","1","3","0","0","0","0","0","0","","0","","CNT","0","1","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",

Re: How can I import text delimited file with a large amount of columns and avoid truncation?

Tom — Tue, 18 Nov 2014 22:10:12 GMT

1) Don't use PROC IMPORT. Just read the file using a data step.

data DICA_new ;

infile 'U:\DICA raw data.txt' dsd truncover lrecl=30000 ;

length var1-var1800 $40 ;

input var1-var1800 ;

run;

2) Don't create the file with all of those unneeded quotes. Then perhaps the lengths of the lines will be small enough for PROC IMPORT to read. You can convert it using a simple data step.

data _null_

infile 'U:\DICA raw data.txt' dsd truncover lrecl=30000 col=cc length=len ;

input @;

length s $200;

file 'U:\DICA_fixed.txt' dsd lrecl=30000 ;

do until(cc >= len);

input s @;

put s @;

end;

put;

run;

Your example data line "fixed" by removing the unneeded quotes is much shorter.

3,,1,,2,1,3,0,0,0,0,0,0,,0,,CNT,0,1,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

Re: How can I import text delimited file with a large amount of columns and avoid truncation?

Ksharp — Wed, 19 Nov 2014 13:12:43 GMT

Unlike Tom. I think you should use proc import , use filename to tell SAS that file 's lrecl is very very long.

filename x 'U:\DICA raw data.txt' lrecl=300000;

proc import datafile=x dbms=dlm out=DICA_new replace;

delimiter=","; getnames=no; datarow=1 ;

run;

Xia Keshan