Help using Base SAS procedures

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

Accepted Solution Solved
Reply
N/A
Posts: 1
Accepted Solution

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

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 ;

r

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","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",


Accepted Solutions
Solution
‎11-18-2014 05:10 PM
Super User
Super User
Posts: 7,074

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

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,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

View solution in original post


All Replies
Solution
‎11-18-2014 05:10 PM
Super User
Super User
Posts: 7,074

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

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,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

Super User
Posts: 10,044

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

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

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