topic Re: proc import error in SAS Procedures

proc import error

shivakrishna — Thu, 20 Jun 2013 17:50:29 GMT

Hi All,

i am getting error while importing the data from csv (proc import)

181 proc import out= cla datafile ="C:\shiva\not.csv" dbms=csv replace;

NOTE: The previous statement has been deleted.

182 Range="not$A5:D10";

-----

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

183 run;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

Thanks

Shiva

Re: proc import error

vomer — Thu, 20 Jun 2013 18:27:28 GMT

Try this:

PROC IMPORT OUT=cla

DATAFILE= "C:\shiva\not.csv"

DBMS=CSV REPLACE;

SHEET="sheet 1";

GETNAMES=YES;

MIXED=YES;

USEDATE=YES;

SCANTIME=YES;

RUN;

Re: proc import error

shivakrishna — Thu, 20 Jun 2013 18:31:38 GMT

thank you vomer

but i need to capture only part of the data (A5:D10)no need full data.

thanks

Shiva

Re: proc import error

data_null__ — Thu, 20 Jun 2013 18:57:47 GMT

SHEET and RANGE have no meaning when DBMS is CSV.

Re: proc import error

vomer — Fri, 21 Jun 2013 03:00:24 GMT

Try this:

PROC IMPORT DATAFILE="c:\<path>\test.xls"

OUT=seq(<for dataset options - see below> )

DBMS=excel REPLACE;

GETNAMES=yes; * variable names are in row 1 ;

RANGE="< range-name OR absolute-range"; * omit if you want to read the entire worksheet;

Of course, you will need to save the file in xls instead of csv.

Re: proc import error

shivakrishna — Fri, 21 Jun 2013 18:46:54 GMT

Hi All

May i know we cannot read part of the data from the CSV file. if yes WHY ?

Thanks

Shiva

Re: proc import error

UrvishShah — Wed, 03 Jul 2013 12:24:05 GMT

Hello,

Here is my try...You can read any feild of data by using OBS options...i think data step works fine rather than proc import...

It will be better if you post your CSV sample file...Here i made simple macro based on your need which extract the 5 to 10 observations...But you need to fix the variable structure in your INPUT statement...

%macro csv_extract(start,end);

/* Design data structure as per your requirement */

data csv_data;

infile "C:\Documents and Settings\ShahUr01\Urvish\test.csv" dlm = "," firstobs = 2;

input name $ age;

run;

/* Extract the required data once it is available in SAS */

data csv_sample;

set csv_data;

if _n_ GE &start. and _n_ LE &end.;

run;

%mend;

%csv_extract(5,10);

-Urvish

Re: proc import error

shivakrishna — Wed, 03 Jul 2013 12:29:01 GMT

Hi urvish thank you for your code but i know by using datastep....

can you please why we are not create part of the data of csv file by using proc step (proc import)

Shiva

Re: proc import error

CharlotteCain — Wed, 03 Jul 2013 13:24:29 GMT

Hi,

Your question gave me an opportunity to research and learn something new. Anyway, if you are looking to import only selected columns from an external file using PROC IMPORT, it seems the RANGE option in specifying the selected columns only works for .xls files alone and not for CSV files. I guess I am probably reiterating the point DN made earlier, however it helps in my understanding as i am a newbie too.

For CSV files, ideally it is best to read using datastep and to be honest in my production work environment the best practice is actually to avoid proc import. Of course, saying that you would want to do what suits you best.

Nevertheless if you do find something works to specify the range in CSV, please post that solution, so we can all learn.

Thanks,

Charlotte

Re: proc import error

antony_allianz — Thu, 04 Jul 2013 04:48:39 GMT

WHY DO WE GIVE RANGE TO READ CSV FILES USING PROC IMPORT? IT IS UNNECESSARY ONE.

Re: proc import error

antony_allianz — Thu, 04 Jul 2013 04:52:39 GMT

IF WE WANT PARTICULAR ROWS WE USE FIRST OBS & OBS.

IF WE WANT PARTICULAR ROWS IN RUN TIME USING MACROS WITH PARAMETERS & USING INFILE WITH IN MACRO DEFINITION.

Re: proc import error

DJChavda — Thu, 04 Jul 2013 10:24:03 GMT

HI Siva,

Try this

option obs=10;

proc import datafile='C:\shiva\not.csv'

out=work.mydata

dbms=csv

replace;

datarow=5;

getnames=yes;

run;

After the execution of the above code reset the option by

option obs=max;

Re: proc import error

shivakrishna — Thu, 04 Jul 2013 10:31:14 GMT

thank you for responding, without using global option we cannot read part of the data form the csv file, and we cannot use range statement inside the proc import step WHY ?

at last my question is why Range statement is not working in the CSV importing file

shiva

Re: proc import error

shivakrishna — Thu, 04 Jul 2013 10:32:17 GMT

thank you antony,

can you please elaborate why it is unnesessary..

shiva

Re: proc import error

LinusH — Thu, 04 Jul 2013 10:48:15 GMT

Because a CSV has a flat file structure, and does not have sheets and cells. An Excel workbook has this information included in it's "metadata".

Re: proc import error

shivakrishna — Thu, 04 Jul 2013 12:05:47 GMT

why ?

Re: proc import error

antony_allianz — Thu, 04 Jul 2013 12:33:00 GMT

Range is applicable for Excel files not for Comma Separated Value files.

Excel file:

PROC IMPORT OUT= WORK.TEST

DATAFILE= "C:\test.xls"

DBMS=EXCEL2000 REPLACE;

RANGE="A4:D5";

GETNAMES=NO;

RUN;

CSV file :

proc import datafile="C:\temp\test.csv"

out=shoes

dbms=csv

replace;

getnames=no;

run;

Re: proc import error

Reeza — Thu, 04 Jul 2013 14:47:01 GMT

CSV files are comma separated variables text flat files. There are no cells and the data structure is that it is separated by commas and has a delimiter to specify the end of line.

RANGE, as specified, is a construct of Excel. Excel knows how to read CSV files, and CSV files usually automatically open in Excel but it is not an excel file and the ranges have no meaning in CSV files. If you want to use RANGE, convert it to an .xls or .xlsx file explicitly.

If your table or information has header information or some other columns you don't want, you'll need to find another way to explicitly read it, using a data step most likely.

Re: proc import error

Scott_Mitchell — Mon, 08 Jul 2013 04:40:28 GMT

Do you really need to use proc import?

I typically use Datasteps to import delimited files. I created a sample CSV which contains 4 variables and imported only 3 of them using the ffollowing:

DATA WORK.TEST;

LENGTH

STUFF1 $ 6

STUFF3 $ 5

STUFF4 $ 9 ;

FORMAT

STUFF1 $CHAR6.

STUFF3 $CHAR5.

STUFF4 $CHAR9. ;

INFORMAT

STUFF1 $CHAR6.

STUFF3 $CHAR5.

STUFF4 $CHAR9. ;

INFILE 'E:\BOOK1.CSV'

FIRSTOBS=2

LRECL=29

ENCODING="WLATIN1"

TERMSTR=CRLF

DLM=","

MISSOVER

DSD ;

INPUT

STUFF1 : $CHAR6.

STUFF3 : $CHAR5.

STUFF4 : $CHAR9. ;

RUN;

Is this useful?

Regards,

Scott