Help using Base SAS procedures

Proc import error

Reply
Contributor
Posts: 21

Proc import error

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;

PROC Star
Posts: 7,364

Proc import error

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

Contributor
Posts: 21

Proc import error

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

Is their any other way i can import excel file?

Regular Contributor
Posts: 233

Re: Proc import error

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

proc print data = work.asd;
run;

Contributor
Posts: 21

Proc import error

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.

PROC Star
Posts: 7,364

Proc import error

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.

Regular Contributor
Posts: 233

Proc import error

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

Regular Contributor
Posts: 233

Proc import error

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

Contributor
Posts: 21

Proc import error

Smiley Sad 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)

Regular Contributor
Posts: 233

Proc import error

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.
Super User
Posts: 9,687

Proc import error

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

Super User
Posts: 9,687

Proc import error

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

Contributor
Posts: 21

Proc import error

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.

Super User
Posts: 9,687

Proc import error

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

Contributor
Posts: 21

Proc import error

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.

Ask a Question
Discussion stats
  • 15 replies
  • 2370 views
  • 0 likes
  • 4 in conversation