topic Importing large TSV files into SAS in New SAS User

Importing large TSV files into SAS

marleeakerson — Thu, 29 Aug 2019 15:16:59 GMT

Hello,

I am having trouble importing a very large TSV file into sas (>1million KB). Does anyone know how to import a file of this size?

Thank you.

Re: Importing large TSV files into SAS

Kurt_Bremser — Thu, 29 Aug 2019 15:24:58 GMT

Importing tab-delimited files with a data step is rather simple. What is your problem?

In case of ERRORs, please post the log, using the {i} button.

Re: Importing large TSV files into SAS

marleeakerson — Thu, 29 Aug 2019 15:37:34 GMT

I dont know if the file is too big or what, but for some reason when i upload the dataset into sas a lot of the columns are concatenated and condensed. here is my code:

proc import datafile="X:\Temp\ABC\ARCOS\arcos-oh-statewide-itemized.tsv" out=work.Oh dbms=tsv replace;

Re: Importing large TSV files into SAS

ballardw — Thu, 29 Aug 2019 16:12:26 GMT

Try DBMS=TAB instead of DBMS=TSV.

also you might want to add the statement:

Guessingrows=32000;

So more rows of data are examined before the variable types, lengths and informats are set by the procedure.

Re: Importing large TSV files into SAS

Reeza — Thu, 29 Aug 2019 16:55:33 GMT

You can import text files uisng proc import or a data step. In a data step you have to specify the formats and types while PROC IMPORT does that but it has to guess. PROC IMPORT is slow because it has to guess the data. Instead, take the code from the log after PROC IMPORT, adjust it as necessary and run the data step instead. That's much faster at importing data because it's no longer guessing.

Re: Importing large TSV files into SAS

Kurt_Bremser — Thu, 29 Aug 2019 16:57:14 GMT

Don't use import. Write a data step according to the documentation you got with the file. You can use the code created by proc import as a starting point.

Re: Importing large TSV files into SAS

PGStats — Thu, 29 Aug 2019 18:23:02 GMT

@marleeakerson wrote:

a lot of the columns are concatenated and condensed.

Make sure the columns are effectively separated by tabs.