Error importing xlsx file

[Blandine]

Hello,

I am trying to import big excel file (4429 rows and 125 columns) into sas but I am getting an error.

Here is my code 

PROC IMPORT DATAFILE="C:\Users\bmrg2\Box Sync\Mizzou 2019\Research\RBI Project\Task 8_Back-casting\NBI Data from Fhwa site\ID 2019\ID19.xlsx"
DBMS=xlsx
OUT=ID19 REPLACE;
SHEET='ID19';
RUN;

PROC PRINT DATA=ID19;
RUN;

Here is the log result:

119 PROC PRINT DATA=ID19;
ERROR: File WORK.ID19.DATA does not exist.
120 RUN;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

I am new and have a hard time figuring out what is wrong.

[ballardw]

You need to show the LOG for the Import step since that is supposed to create the data set that Proc Print should display.

[jimbarbour]

Just a basic check before we go to far:  Is the sheet name ID19?  Many times, the sheet names will not be the same as the file name, so, although basic, it's important to check. The sheet name should be spelled exactly as listed at the bottom of the Excel window.  In the below example, I have four sheets (Summary, Pivot, Data, and Values), any one of which could be brought in via Proc Import.

Jim

[Blandine]

Yes the sheet name is ID19 same as the file name.

[jimbarbour]

OK, good.

Next question:  Was the Excel spreadsheet closed when you tired the import?  If not, SAS will give your the (very) misleading message "does not exist" when in fact all that needs to occur is to close the Excel spreadsheet.  Please make sure the spreadsheet is closed and try again.

Jim

[Blandine]

It was opened but when I close the excel file I get this log result:

26 proc import datafile="C:\Users\bmrg2\Box Sync\Mizzou 2019\Research\RBI Project\Task
26 ! 8_Back-casting\NBI Data from Fhwa site\ID 2019\ID19.xls"
27 out=newID19
28 DBMS=xls
29 replace;
30 Sheet="sheet1";
31 Getnames=Yes;
32 run;

ERROR: Physical file does not exist, C:\Users\bmrg2\Box Sync\Mizzou 2019\Research\RBI
Project\Task 8_Back-casting\NBI Data from Fhwa site\ID 2019\ID19.xls.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.04 seconds
cpu time 0.00 seconds

[jimbarbour]

Well, let's check the basics.  Can you post a screen capture of a Windows Explorer session showing that directory and that file?  See example, below.  We should confirm that the file hasn't been accidentally moved or misspelled before we look at other issues.  

After that, we should check the sheet name.  Sheet1 is the default and it's highly likely that is in fact the sheet name, but we should still check.

Jim

[Kurt_Bremser]

The last ERROR or WARNING is always the least important, the first the most important. Fixing the first usually cleans up most, if not all, following problems.

So you fix your code top-down, and when you need help, you need to show the log from the whole code of the first step that causes a problem.

[Blandine]

So this is a new one I wrote since I lost what I did last week

/*PROGRAM FOR RBI RESEARCH STATE OF IDAHO OCTOBER 10 2020*/
proc import datafile="C:\Users\bmrg2\Box Sync\Mizzou 2019\Research\RBI Project\Task 8_Back-casting\NBI Data from Fhwa site\ID 2019\ID19.xls"
out=outID19
DBMS=xls
Sheet="sheet1";
Getnames=Yes;
run;

here is the log error


NOTE: Copyright (c) 2016 by SAS Institute Inc., Cary, NC, USA.
NOTE: SAS (r) Proprietary Software 9.4 (TS1M6)
Licensed to THE CURATORS OF THE UNIV OF MISSOURI - T&R SFA, Site 70084888.
NOTE: This session is executing on the X64_10PRO platform.



NOTE: Analytical products:

SAS/STAT 15.1
SAS/ETS 15.1
SAS/IML 15.1
SAS/QC 15.1

NOTE: Additional host information:

X64_10PRO WIN 10.0.19041 Workstation

NOTE: SAS initialization used:
real time 3.51 seconds
cpu time 2.04 seconds

NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

NOTE: The SAS System stopped processing this step because of errors.
1 proc import datafile="C:\Users\bmrg2\Box Sync\Mizzou 2019\Research\RBI Project\Task
1 ! 8_Back-casting\NBI Data from Fhwa site\ID 2019\ID19.xls"
2 out=outID19
3 DBMS=xls
4 Sheet="sheet1";
-----
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, DATAFILE, DATATABLE, DBMS, DEBUG,
FILE, OUT, REPLACE, TABLE, _DEBUG_.

ERROR 76-322: Syntax error, statement will be ignored.

5 Getnames=Yes;
6 run;


NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

NOTE: The SAS System stopped processing this step because of errors.
7 proc import datafile="C:\Users\bmrg2\Box Sync\Mizzou 2019\Research\RBI Project\Task
7 ! 8_Back-casting\NBI Data from Fhwa site\ID 2019\ID19.xls"
8 out=outID19
9 DBMS=xls
10 Sheet="sheet1";
-----
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, DATAFILE, DATATABLE, DBMS, DEBUG,
FILE, OUT, REPLACE, TABLE, _DEBUG_.

ERROR 76-322: Syntax error, statement will be ignored.

11 Getnames=Yes;
12 run;


13 /*PROGRAM FOR RBI RESEARCH STATE OF IDAHO OCTOBER 10 2020*/
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

NOTE: The SAS System stopped processing this step because of errors.
14 proc import datafile="C:\Users\bmrg2\Box Sync\Mizzou 2019\Research\RBI Project\Task
14 ! 8_Back-casting\NBI Data from Fhwa site\ID 2019\ID19.xls"
15 out=outID19
16 DBMS=xls
17 Sheet="sheet1";
-----
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, DATAFILE, DATATABLE, DBMS, DEBUG,
FILE, OUT, REPLACE, TABLE, _DEBUG_.

ERROR 76-322: Syntax error, statement will be ignored.

18 Getnames=Yes;
19 run;

[jimbarbour · Accepted solution]

You need a semi-colon after DBMS=xls.

proc import datafile="C:\Users\bmrg2\Box Sync\Mizzou 2019\Research\RBI Project\Task 8_Back-casting\NBI Data from Fhwa site\ID 2019\ID19.xls"
out=outID19
DBMS=xls
;  /*  Semi colon needed here  */
Sheet="sheet1";
Getnames=Yes;
run;

Jim

[Blandine]

So I just fixed it as you said and I realize my file name extension was "xlsx" and not "xls". I corrected it this is what I get in the log which I supposed my data were imported. However, how do I move it from the "work" library to a permanent library? sorry for my dumb question

33 /*PROGRAM FOR RBI RESEARCH STATE OF IDAHO OCTOBER 10 2020*/
34 proc import datafile="C:\Users\bmrg2\Box Sync\Mizzou 2019\Research\RBI Project\Task
34 ! 8_Back-casting\NBI Data from Fhwa site\ID 2019\ID19.xlsx"
35 out=newID19
36 DBMS=xlsx
37 replace;
38 Sheet="sheet1";
39 Getnames=Yes;
40 run;

NOTE: One or more variables were converted because the data type is not supported by the V9
engine. For more details, run with options MSGLEVEL=I.
NOTE: The import data set has 4493 observations and 123 variables.
NOTE: WORK.NEWID19 data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 5.40 seconds
cpu time 5.36 seconds

[Blandine]

Never mind a simple copy and paste did the work.

Thanks a lot

[jimbarbour]

Excellent.  Sounds like we're all set then..👍

Jim

[Kurt_Bremser]

You can use libraries wherever you use datasets, so you only need to add the library in your PROC IMPORT statement:

proc import
  datafile="C:\Users\bmrg2\Box Sync\Mizzou 2019\Research\RBI Project\Task 8_Back-casting\NBI Data from Fhwa site\ID 2019\ID19.xlsx"
  out=permlib.newID19
  DBMS=xlsx
  replace
;