How to import large number of variables from a tab-delimited file

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

How to import large number of variables from a tab-delimited file

Hi, I have a tab-delimted txt file and want to import it to SAS. It has 700 rows and over 12000 columns. After I submitted these codes, it only returned the first 1945 columns, and no warnings!! Any magic solution? BTW, i am using SAS9.4:

proc import datafile="C:\Users\for SAS.txt"

     out=all

     dbms=dlm

     replace;

guessingrows=32000;

     delimiter='09'x;

run;

thanks,

Zhengming


Accepted Solutions
Solution
‎02-09-2015 08:58 PM
Super User
Super User
Posts: 7,054

Re: How to import large number of variables from a tab-delimited file

You are probably better off just reading it in yourself.

data values (keep=row col name cvalue value);

  infile out dsd truncover lrecl=300000 length=ll col=cc ;

  length row col 8 name $32 cvalue $32 value 8;

  array names (3000) $32 _temporary_ ;

  retain row -1 ;

  row+1;

  if _n_=1 then do col=1 by 1 until(cc >= ll) ;

    input name @;

    names(col) = name ;

  end;

  else do col=1 by 1 until(cc >= ll) ;

    input cvalue @ ;

    name = names(col);

    value = input(cvalue,??comma32.);

    output;

  end;

run;

View solution in original post


All Replies
PROC Star
Posts: 7,474

Re: How to import large number of variables from a tab-delimited file

The maximum lrecl that proc import will accept is 32767. With more than 12000 variables, I am sure that your record length exceeds that width.

You will probably have to import the file by writing and using a datastep.

Contributor
Posts: 40

Re: How to import large number of variables from a tab-delimited file

I opened the file in Excel and save it as Excel Workbook. Then use Proc Import with dbms=xlsx and it is done.

thanks,

Zhengming

Super User
Posts: 11,343

Re: How to import large number of variables from a tab-delimited file

You may want to examine that imported file for changes such as variables that you expect to be character that should have leading zeroes were imported correctly. Long account "numbers" may be appearing as scientific notation and if they exceeded 12 or 16 digits(depending on OS) may be incorrect and dates are another place for possible discrepancies. As in variables that weren't supposed to be dates such as 5-12 becoming 5/12/14 or so.

Excel will occasionally be "helpful" and assume things about you data that aren't quite so and change values when saving a file.

Contributor
Posts: 40

Re: How to import large number of variables from a tab-delimited file

Well said!

Z

Super User
Posts: 10,035

Re: How to import large number of variables from a tab-delimited file

Maybe you can also use proc import to get it . But I didn't test it ,just guessing.

filename x "C:\Users\for SAS.txt" lrecl=323324234344 ;

proc import datafile=x

     out=all

     dbms=dlm

     replace;

guessingrows=32000;

     delimiter='09'x;

run;

Solution
‎02-09-2015 08:58 PM
Super User
Super User
Posts: 7,054

Re: How to import large number of variables from a tab-delimited file

You are probably better off just reading it in yourself.

data values (keep=row col name cvalue value);

  infile out dsd truncover lrecl=300000 length=ll col=cc ;

  length row col 8 name $32 cvalue $32 value 8;

  array names (3000) $32 _temporary_ ;

  retain row -1 ;

  row+1;

  if _n_=1 then do col=1 by 1 until(cc >= ll) ;

    input name @;

    names(col) = name ;

  end;

  else do col=1 by 1 until(cc >= ll) ;

    input cvalue @ ;

    name = names(col);

    value = input(cvalue,??comma32.);

    output;

  end;

run;

Contributor
Posts: 40

Re: How to import large number of variables from a tab-delimited file

Great idea! It works. Since my data is tab-delimited, I added this "delimiter='09'x;" and changed "??comma32." to "??best32." I also use these codes to transpose the data into my original format:

proc transpose data=values out=values2;

var cvalue;

id name;

by row notsorted;

run;

thanks,

Z

Super User
Super User
Posts: 7,054

Re: How to import large number of variables from a tab-delimited file

BEST32 is a FORMAT, not an INFORMAT.  It will be translated by SAS into the F32. (or 32.) informat.  The COMMA format is better as it will also read values with commas as thousands separators.

🔒 This topic is solved and locked.

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

Discussion stats
  • 8 replies
  • 885 views
  • 0 likes
  • 5 in conversation