Proc import error

[Sudtej]

i am getting below error:

Error: file_IMEX_.'feb2012$'n.data does not exist. i have chcked again &again for any spaces in sheet name still i am facing the same error.

Just to mention,Actual data contains 35 columns and 1000 rows and multiple sheets(have tried for improting another sheets but got the same erro)

Hi,

i am unable to import the attached file using proc import. below is my code

PROC IMPORT OUT= WORK.asd

            DATAFILE= "C:\Tracker\ABC.xls"

            DBMS=EXCEL REPLACE;

     SHEET="'feb2012$'";

     GETNAMES=YES;

     MIXED=YES;

RUN;

[art297]

I would try it one or two more ways:

1: remove the set of single quotes so that the sheet name is only surrounded by one set of quotes

2. if that alone doesn't work, try adding the letter n after the right most quote

[Sudtej]

I tried above mentioned ways but still getting the same error....

Is their any other way i can import excel file?

[Hima]

PROC IMPORT OUT= work.asd
            DATAFILE= "C:\Documents and Settings\abc.xls"
            DBMS=excel REPLACE;
   sheet = "feb2012";
RUN;

proc print data = work.asd;
run;

[Sudtej]

Hima,

Tried the code, but it is giving below error:

Error: file_IMEX_.'feb2012$'n.data does not exist.

its very strange as i could able to import other files which has a similar structure.

[art297]

Did you try it exactly as Hima suggested? i.e.,

PROC IMPORT OUT= WORK.asd

         DATAFILE= "C:\Tracker\ABC.xls"

         DBMS=EXCEL REPLACE;

     SHEET="feb2012";

     GETNAMES=YES;

      MIXED=YES;

RUN;

Worked for me!  Are you sure the file is in the exact location and named as specified in the datafile statement?  I was able to import your example file with no problem.

[Hima]

This is strange. It worked for me on PC SAS. I found this link that might help you.

http://support.sas.com/kb/19/293.html

[Hima]

Change  SHEET="'feb2012$'"; to  range='feb2012$';

[Sudtej]

still the same error

Is their any other way?..maybe filename statement but dt know how to specify sheet name in filename stmt( but i need to spcify 35 column names)

[Hima]

Let's try this.

1. Select the data in the excel.
2. Copy and paste it to notepad.
3. Open a new excel spread sheet
4. Copy the data from notepad to the new excel.
5. Save the excel.
6. Run the proc import code.

[Ksharp]

It is so weird. I can replicate the same ERROR as yours.

But when I change DBMS from excel to xls, all is going on well.

PROC IMPORT OUT= WORK.asd
            DATAFILE= "C:\ABC.xls"
            DBMS=XLS REPLACE;
     SHEET='feb2012';
     GETNAMES=YES;
     MIXED=YES;
RUN;

Ksharp

[Ksharp]

I also found a interesting thing.

DBMS=xls + GETNAMES=no;  will get variate name as A B C D E F G ...........

which is the excel's column name.

I remember this question has been asked by Linlin, Now I found the answer.

Ksharp

[Sudtej]

at least sombody is getting the same error as mine...

@Kasharp....i tried ur way but i got an error as DBMS type XLS not valid for import (am using sas 9.1.3)

My excel sheet for 1st row contains (name,1,2,3...30 )where 1,2..30 repersents days....this xls every day automatically get upadated by sas program based on the dates.

[Ksharp]

Are your SAS under UNIX system?

But Finally I find the answer. You need define a range.

http://support.sas.com/kb/10/118.html

PROC IMPORT OUT= WORK.asd

            DATAFILE= 'C:\ABC.xls'

            DBMS=excel REPLACE;

     range='feb2012$A1:F20'n;

     GETNAMES=NO;

     MIXED=YES;

RUN;

Ksharp

[Sudtej]

Thanks Ksharp, its working!!!

Can you please tell me in range='feb2012$A1:F20'n; what A and F spcifies?

i need to import 30 columns and 1000 rows.