topic Re: Error importing xlsx file in New SAS User

Error importing xlsx file

Blandine — Fri, 02 Oct 2020 23:00:14 GMT

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.

Re: Error importing xlsx file

ballardw — Fri, 02 Oct 2020 23:27:37 GMT

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

Re: Error importing xlsx file

jimbarbour — Fri, 02 Oct 2020 23:49:54 GMT

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

Re: Error importing xlsx file

Kurt_Bremser — Sat, 03 Oct 2020 06:25:44 GMT

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.

Re: Error importing xlsx file

Blandine — Fri, 09 Oct 2020 14:50:31 GMT

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

Re: Error importing xlsx file

Blandine — Fri, 09 Oct 2020 14:54:38 GMT

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;

Re: Error importing xlsx file

jimbarbour — Fri, 09 Oct 2020 14:54:54 GMT

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

Re: Error importing xlsx file

jimbarbour — Fri, 09 Oct 2020 15:00:51 GMT

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

Re: Error importing xlsx file

Blandine — Fri, 09 Oct 2020 15:01:48 GMT

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

Re: Error importing xlsx file

jimbarbour — Fri, 09 Oct 2020 15:12:43 GMT

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

Re: Error importing xlsx file

Blandine — Fri, 09 Oct 2020 15:23:24 GMT

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

Re: Error importing xlsx file

Blandine — Fri, 09 Oct 2020 15:26:19 GMT

Never mind a simple copy and paste did the work.

Thanks a lot

Re: Error importing xlsx file

Kurt_Bremser — Fri, 09 Oct 2020 15:27:33 GMT

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
;

Re: Error importing xlsx file

jimbarbour — Fri, 09 Oct 2020 15:28:16 GMT

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

Jim