topic Proc import for a csv file in New SAS User

Proc import for a csv file

Ranjeeta — Mon, 22 Oct 2018 19:52:41 GMT

PROC IMPORT OUT= sample1
DATAFILE= "Y:\sample.csv" ;
DBMS=csv REPLACE;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;

Can i use the scan text, usedat and scantime functions

Re: Proc import for a csv file

Reeza — Thu, 03 Oct 2019 21:16:15 GMT

Editor's Note: This 10-minute tutorial by @Panagiotis shows you how to import CSV files into SAS:

No, I believe those are specific to other formats. You can use GUESSINGROWS. Set it to MAX and you'll usually get better results.

You do need to specify the DBMS before the semicolon I believe.

PROC IMPORT OUT= sample1 
                          DATAFILE= "Y:\sample.csv" 
                          DBMS=CSV 
                          REPLACE;
GUESSINGROWS=MAX;
RUN;

https://documentation.sas.com/?docsetId=proc&docsetTarget=n1qn5sclnu2l9dn1w61ifw8wqhts.htm&docsetVersion=9.4&locale=en

@Ranjeeta wrote:

PROC IMPORT OUT= sample1
DATAFILE= "Y:\sample.csv" ;
DBMS=csv REPLACE;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;

Can i use the scan text, usedat and scantime functions

Re: Proc import for a csv file

Ranjeeta — Mon, 22 Oct 2018 20:27:13 GMT

Thankyou the file im trying to import is very large and therefore the proc import step does not work

Would you be able to suggest how to import it another way?

Re: Proc import for a csv file

Reeza — Mon, 22 Oct 2018 20:32:39 GMT

Not sure how that's possible. I've imported GB text files using PROC IMPORT.

What exactly does 'does not work' mean? Are you getting errors? It starts and doesn't complete?

Are you using SAS UE or a full installation of SAS?

If SAS UE, it has RAM limitations, but you can add more (VM settings) and you can redirect your work folder to get around some of that.

@Ranjeeta wrote:
Thankyou the file im trying to import is very large and therefore the proc import step does not work

Would you be able to suggest how to import it another way?

Re: Proc import for a csv file

Ranjeeta — Mon, 22 Oct 2018 20:38:13 GMT

Im using EG rather than base sas

The file keeps running but there is no import completed

The file size is 1.69 GB

Re: Proc import for a csv file

Reeza — Mon, 22 Oct 2018 20:44:27 GMT

Guessingrows makes it take a long time because it scans the full file first. Try reducing it to a million records or less then. If you have a record layout reading the file using a data step would be significantly faster. I often will read the first million records, build myself a data step (check the log after proc import) and then import the data that way.

Re: Proc import for a csv file

ballardw — Tue, 23 Oct 2018 15:25:51 GMT

@Ranjeeta wrote:
Im using EG rather than base sas

The file keeps running but there is no import completed

The file size is 1.69 GB

EG doesn't matter.

The acceptable values for the option guessingrows is 1 to 2147483647 (or MAX). Unless you have very poor data values around 100000 to 1000000 likely will work. You might want to copy the data step code generated that appears in the log and save it. If you need to set the length of one of your variables larger it may be quicker to set the informat for that variable to a larger length and run the data step with the modified code instead of proc import.

Re: Proc import for a csv file

Tom — Mon, 22 Oct 2018 23:43:39 GMT

@Ranjeeta wrote:

PROC IMPORT OUT= sample1
DATAFILE= "Y:\sample.csv" ;
DBMS=csv REPLACE;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;

Can i use the scan text, usedat and scantime functions

A CSV file is just a text file. Write your own data step to read it.

If you don't have any documentation then look at the top of the file and figure it out yourself.

data _null_;
  infile "Y:\sample.csv"  obs=5;
  input;
  list;
run;

Re: Proc import for a csv file

Ranjeeta — Fri, 26 Oct 2018 13:33:21 GMT

Thankyou The proc import worked for one of the files it took 20 minutes but I believe there is a difference in speen in base sas vs SAS EG

Re: Proc import for a csv file

ballardw — Fri, 26 Oct 2018 16:47:06 GMT

@Ranjeeta wrote:

Thankyou The proc import worked for one of the files it took 20 minutes but I believe there is a difference in speen in base sas vs SAS EG

Yes there is a speed difference because somewhere EG has to use the actual SAS engines. EG is basically just a front end. So there is a little extra time in anything just for EG to talk to the engines. In many places you have EG using SAS on a server, so server performance issues may cause additional delays such as network performance, your user share of processing time or space limits and such.

If you have to routinely deal with files of this size you might talk to your SAS Admin about the settings for your access to see if there are settings that could be adjusted for better performance. Perhaps the general IT staff, if any, need to get involved about other system constraints.