topic Re: Import large csv problem in SAS Data Management

Import large csv problem

evgenys — Sat, 28 Jan 2017 13:44:33 GMT

Hi all,
I'm trying to import CSV file of 4 million rows and ~150 columns that order of columns I don't know.
It is impossible to do guessingrows for such a file.
And, I get cut character columns.

Any ideas what can I do?

Re: Import large csv problem

Kurt_Bremser — Sat, 28 Jan 2017 13:59:43 GMT

Very simple. Write a data step according to the file description.

To get help in this, you best post that description here.

Re: Import large csv problem

evgenys — Sat, 28 Jan 2017 14:51:25 GMT

As I wrote. I didn't know the order and some times discription of columns... becouse of this I can't write data step....

Re: Import large csv problem

Kurt_Bremser — Sat, 28 Jan 2017 14:58:50 GMT

Then DEMAND the description from whoever gave you that file.

Your only other option is to run proc import with a large enough guessingrows value, and hope for the best.

Unless you want to inspect all rows with the good old eyeballs Mk 1.

Re: Import large csv problem

Reeza — Sat, 28 Jan 2017 15:03:47 GMT

There isn't much of a choice then. I suggest the standard process of using proc import to generate code. Copy the code from the log and customize according to your errors until they're gone.

I would recommend setting GUESSINGROWS to 1 million for initial proc import.

You can limit total amount read using OBS option.

Option obs=1500000;

Theb reset after:

option obs=Max;

Re: Import large csv problem

evgenys — Sat, 28 Jan 2017 15:09:58 GMT

I tried GUESSINGROWS 4 million ...it doesn't end.😃

Re: Import large csv problem

Reeza — Sat, 28 Jan 2017 15:15:49 GMT

If 1 million doesn't get it that would be very surprising.

I suggested 1.5,million.

Re: Import large csv problem

Kurt_Bremser — Sat, 28 Jan 2017 15:18:10 GMT

@evgenys wrote:
I tried GUESSINGROWS 4 million ...it doesn't end.😃

Oh yeah, 4 million rows * 150 columns takes a lot of time to inspect. It is what happens when you try something stupid.

Get back to the data source and demand the description, and tell them that the data is useless without it.

Re: Import large csv problem

evgenys — Sat, 28 Jan 2017 15:41:37 GMT

😃
I did it first...but before it done I have to cope with it...

Re: Import large csv problem

TomKari — Sat, 28 Jan 2017 16:37:49 GMT

This isn't an easy problem!

Do any of your fields have internal commas? If not, then you could use (untested):

data _null_;
retain MaxCols 0;
infile x end=LastRec;
input;
ColCount = count(_infile_, ",") + 1;
if ColCount > MaxCols then MaxCols = ColCount;
if LastRec then call symput("ColCount", put(MaxCols, best8.));
run;

This should give you the highest number of columns in your file.

Then maybe something along the lines of:

%macro GetLen;
data _null_;
retain MaxLen 0;
infile x end=LastRec;
input;
%do &i = 1 to &ColCount;
ColLen = length(scan(_infile_, &i))
if ColLen > MaxLen then MaxLen = ColLen;
%end;
if LastRec then call symput("ColLen", put(MaxLen, best8.));
run;
%mend;
%GetLen;

And finally:

%macro GetData;
data Want;
length Col1-Col&ColCount. $&ColLen.;
infile x;
input;
%do &i = 1 to &ColCount;
Col&i. scan(_infile_, &i);
%end;
run;
%mend;
%GetData;

Good luck!

Tom

Re: Import large csv problem

evgenys — Sat, 28 Jan 2017 16:53:55 GMT

Thanks Tom. I'll try it tomorrow.

Re: Import large csv problem

Tom — Sat, 28 Jan 2017 18:49:04 GMT

You don't need to know what is in a CSV file to read it in as character strings. If you don't even know how many columns there are just use a larger number (150 in the example below) than you expect. If the last column is not empty then increase the number and read the file again.

data temp (compress=yes);
   infile 'myfile.csv' dsd truncover firstobs=2 ;
   length x1-x150 $200 ;
   input x1-x150;
run;

You can then analyze the character strings yourself and make your own decision on what is in it.

Find the maximum length (if you find any with maximum length close to 200 then you might want to use more then $200 in the step above).
Check if they can be converted to a number by using INPUT function with COMMA32. informat.
Check if they can by converted to a date, time, or datetime by using ANYDTDTE, ANYDTTME, and ANYDTDTM informats, respectively.

If the first line has variable names then you could read that line in separately and use it to rename the variables.

Re: Import large csv problem

PGStats — Sun, 29 Jan 2017 03:20:59 GMT

Here is a quick way to take a 1% random sample of the records using a line pointer control in the input statement

data sample;
length str $200;
infile "&sasforum\datasets\frame.csv" truncover line=lineNo;
linePt = ceil(2 * 100 * rand("uniform"));
input #linePt str&;
line + lineNo;
keep line str;
run;

Re: Import large csv problem

ballardw — Mon, 30 Jan 2017 16:40:17 GMT

My rough stab would be to run proc import with guessingows in the 32000 range. The save and inspect the generated datastep code.

I would likely increase the lengths of character variables in case the generated 73 or such doesn't quite work for all of them, likely around 10 percent added.

But if you don't have a document that says what any of the columns are then what are you going to do with this data file? Unless you have column headers long enough to be explanatory it may be hard to tell what anything else is.

Re: Import large csv problem

evgenys — Mon, 30 Jan 2017 17:24:38 GMT

Let see... you have a software that it's output CSV file that cat change. You do know what you get.. and you can't change your import code every time.That's you must have a sutable function.

Re: Import large csv problem

Kurt_Bremser — Mon, 30 Jan 2017 19:51:24 GMT

Rapidly changing file structures are a VERY BAD IDEA, as they only cause unnecessary work and are a sign for no design or a very idiotic one.

For one-shots, PROC IMPORT is the way to go, but let me tell you that even for one-off flat files that I have to deal with, I always get the same type of documentation that I get for those that will be created and imported daily.

So you either learn to live with the shortcomings of PROC IMPORT, or go through a lot of work determining the file structure by trial-and-error, or tell the idiot that heaped that task on you to finally grow a brain.

Re: Import large csv problem

ballardw — Mon, 30 Jan 2017 20:32:28 GMT

Point out to management how much "extra time" this process takes for one iteration and the costs. Then the next time the additional time and associated costs. And the next time. Repeat as needed.

It may sink into someone head that having a process with an order and controls may actually make financial sense to the organization.

I once worked as a contractor doing some data analysis for a client. The at one point asked about the typical $200 to $400 monthly fee we charged in programming. When they learned it was because we had to reprogram processes each month due to file column order and content changes the data stabilized pretty quickly.

Note that we did not have that issue with the client's software side of the house only the hardware folks.

Re: Import large csv problem

evgenys — Mon, 30 Jan 2017 21:04:54 GMT

One of the reasons for such problem is (that's my problem too) if you integrate new software you'll run it 1000s times with 100s of different output files.
Moreover, I expected from software like sas if I've written separated by '','' it would read data between two'','' .

Re: Import large csv problem

Reeza — Mon, 30 Jan 2017 22:38:37 GMT

@evgenys wrote:

Moreover, I expected from software like sas if I've written separated by '','' it would read data between two'','' .

That's what it does. How does SAS not do that?

Honestly your question and what your actual issue is unclear. We're making standard guesses and comments but beyond you need to know your data what else can we say. I'm 99% sure any other language operates pretty much the same way with a file that is unspecified. This is also why people use databases.

Re: Import large csv problem

Kurt_Bremser — Tue, 31 Jan 2017 10:54:29 GMT

Moreover: if I had to make a guess, I'd say that 80% of my work now involves documentation, either writing or reading. The actual programming consumes very little time, partly owing to the tools I created to make my work easier, but mostly because of the documentation!