topic Re: Effective way to import huge CSV file in SAS Procedures

Effective way to import huge CSV file

RamKumar — Wed, 04 Mar 2015 12:00:58 GMT

What is the best way to import the .csv effectively with less time consumption?

I've several thousands of records in my .CSV and I used to import the file as follows. If the file is large (say 200 MB) it is taking hours to complete. Can it be complete in minutes by tweaking the import process?

filename rawdata "/data/04001_Compa_12312014.csv" termstr=crlf lrecl=32760;

proc import datafile=rawdata out=rawdata1 dbms=csv replace;

guessingrows=3000000;

run;

Thanks in advance for your inputs.

Re: Effective way to import huge CSV file

data_null__ — Wed, 04 Mar 2015 12:05:46 GMT

Just write a data step to read it. You will need to know the field attributes.

Re: Effective way to import huge CSV file

RamKumar — Wed, 04 Mar 2015 12:12:05 GMT

Even reading the .csv file is also time consumes. I used to get warning message like 'File not loaded completely' when I try to open large volume file. I don't know the field attributes without reading the file though.

Any other suggestions?

Re: Effective way to import huge CSV file

data_null__ — Wed, 04 Mar 2015 12:31:21 GMT

RamKumar wrote:

I don't know the field attributes without reading the file though.

Any other suggestions?

You're kidding right? You have 200MB of data and you know nothing about the contents and have no documentation.

Re: Effective way to import huge CSV file

gergely_batho — Wed, 04 Mar 2015 12:39:11 GMT

- Decreasing guessingrows (if possible)

- compress=yes

How much time does it take to simply copy this file with operating system command? The speed of import depends largely on the speed of the hard drive.

Re: Effective way to import huge CSV file

RamKumar — Wed, 04 Mar 2015 12:57:26 GMT

How much time does it take to simply copy this file with operating system command? It takes only 5-7 seconds to copy a file in UNIX box. I did this using cp command.

Re: Effective way to import huge CSV file

Kurt_Bremser — Wed, 04 Mar 2015 13:20:42 GMT

Forget proc import. Take the documented structure(!!!), write a data step, and it won't take much longer than a simple cp.

I read files up to > 10 GB, and I rarely drop below 50MB/sec throughput during the data step. And that's a p520, almost 10 years old.

If you get 200 MB of undocumented data, print it out on paper and use that stack to whack the idiot that delivered it over the head. Maybe that gets him(her) thinking.

OTOH, how do you technically read the file? You stated that it takes 5-7 secs for the copy. Is SAS running on the same UNIX machine, or do you read the file over the network?

Re: Effective way to import huge CSV file

RamKumar — Wed, 04 Mar 2015 14:10:59 GMT

Is SAS running on the same UNIX machine, or do you read the file over the network? Yes, SAS running on UNIX server.

I've used my proc import in SAS EG. I could see a difference after I put a compress option and trimming one zero's from guessing zeros as Gergely suggests.

Re: Effective way to import huge CSV file

Tom — Wed, 04 Mar 2015 16:37:56 GMT

If you are using EG where is the SAS server relative to the file? If your SAS server is remote from the machine running EG then EG might be uploading the file across the network to the SAS server.

Much better to store the file on a disk that the SAS server can see and read it from there.

Do not use PROC IMPORT to read a CSV file. It will have to guess at how to read the file. If you really have no idea what is in the file then read the first few lines and look at them. Then write your DATA step to read the file.

Re: Effective way to import huge CSV file

SASKiwi — Wed, 04 Mar 2015 18:34:50 GMT

In my experience using guessingrows with a large value can drastically slow down the import of CSV files.

Behind PROC IMPORT SAS builds DATA step code when reading CSVs. You can see this in the SAS log. Just copy this DATA step code into your SAS editor and run it. You will be amazed how much quicker it is.

Re: Effective way to import huge CSV file

RamKumar — Thu, 05 Mar 2015 06:12:29 GMT

If you really have no idea what is in the file then read the first few lines and look at them - how to read first few lines in SAS?

Re: Effective way to import huge CSV file

Kurt_Bremser — Thu, 05 Mar 2015 07:08:30 GMT

It is a .csv in UNIX, so head -5 filename from the commandline where you did the cp will show you the first 5 lines. This way you can make an educated guess about the record length.

Then do

data test;

infile "your_file" lrecl=your_guess obs=how_may_you_want;

length dataline $ your_guess;

input;

dataline = _infile_;

run;

This will give you a file with the complete lines to look at.

Re: Effective way to import huge CSV file

SASKiwi — Thu, 05 Mar 2015 07:14:26 GMT

Look at the SAS code being generated in the SAS log by CSV PROC IMPORT as I have explained above...you can use that directly to read in the file.

To read the first 10 lines:

data test;

infile rawdata obs = 10;

input;

put _infile_;

run;

Re: Effective way to import huge CSV file

RamKumar — Thu, 05 Mar 2015 07:25:55 GMT

can we create a dataset with first few observations instead of reading it in log?

Re: Effective way to import huge CSV file

Kurt_Bremser — Thu, 05 Mar 2015 07:37:34 GMT

Look at my answer; the obs= option in the infile statement does the trick.

Re: Effective way to import huge CSV file

gergely_batho — Thu, 05 Mar 2015 07:40:30 GMT

After the input statement we need to put a variable name. Otherwise the created dataset (test) does not have columns.

data test;

infile rawdata obs = 10;

input someColName $1000.;

put _infile_;

run;

But I think @KurtBremser original suggestion was to use the data step generated by the PROC IMPORT: modify it by including an obs=10 to the infile statement.

Re: Effective way to import huge CSV file

Tom — Thu, 05 Mar 2015 12:58:18 GMT

You can read it into variables using dummy variable names.

For example to read first 20 columns for the first 20 rows.

Use FIRSTOBS=2 to skip the header line.

data sample;

infile 'myfile.csv' dsd truncover lrecl=300000 firstobs=2 obs=20 ;

length x1-x20 $200 ;

input x1-x20 ;

run;

Re: Effective way to import huge CSV file

data_null__ — Thu, 05 Mar 2015 13:27:47 GMT

Isn't TRUNCOVER for FORMATTED and/or COLUMN?

Since delimited input is a form of LIST input would there ever be a situation where TRUNCOVER would be needed. MISSOVER seems more appropriate.

I know it serves the same role here but it seems wrong.

Re: Effective way to import huge CSV file

Tom — Thu, 05 Mar 2015 13:32:58 GMT

In my opinion MISSOVER is deprecated and should never be used unless you really do want to throw away short values.

Re: Effective way to import huge CSV file

data_null__ — Thu, 05 Mar 2015 14:00:03 GMT

The point is there is no such thing as "short values" when reading with LIST input.