Errors in Importing Excel Data from a Single Worksheet

[mkSAS1]

Dear SAS  community,

I have the following asignment:

In this practice, you will create a table that contains a copy of the data that from an Excel workbook. The Excel workbook contains a single worksheet.

1. Open p102p01.sas from the practices folder. Complete the PROC IMPORT step to read eu_sport_trade.xlsx. Be sure the replace FILEPATH with the path to your EPG1V2 > data folder.

2. Create a SAS table named eu_sport_trade and replace the table if it exists.

3. Modify the PROC CONTENTS code to display the descriptor portion of the eu_sport_trade table.

4. Submit the program, and then view the output data and the results.

=========

my code was set up as follows and got errors:

libname pps "/home/u63717210/EPG1V2/practices";

proc import Datafile= "pps/p102p01.sas/eu_sport_trade.xlsx"

DBMS=xlsx 

out=pps.eu_sport_trade replace;

run;

proc contents data=pps.eu_sport_trade;

run;

LOG:

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

68

69 ***********************************************************;

70 * LESSON 2, PRACTICE 1 *;

71 * a) Complete the PROC IMPORT step to read *;

72 * EU_SPORT_TRADE.XLSX. Create a SAS table named *;

73 * EU_SPORT_TRADE and replace the table *;

74 * if it exists. *;

75 * b) Modify the PROC CONTENTS code to display the *;

76 * descriptor portion of the EU_SPORT_TRADE table. *;

77 * Submit the program, and then view the output data *;

78 * and the results. *;

79 ***********************************************************;

80 libname pps "/home/u63717210/EPG1V2/practices";

NOTE: Libref PPS was successfully assigned as follows:

Engine: V9

Physical Name: /home/u63717210/EPG1V2/practices

81 proc import Datafile= "pps/p102p01.sas/eu_sport_trade.xlsx"

82 DBMS=xlsx

83 out=pps.eu_sport_trade replace;

84 run;

ERROR: Physical file does not exist, /pbr/biconfig/940/Lev1/SASApp/pps/p102p01.sas/eu_sport_trade.

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

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.00 seconds

user cpu time 0.00 seconds

system cpu time 0.00 seconds

memory 2657.62k

OS Memory 22260.00k

Timestamp 07/01/2026 12:55:07 PM

Step Count 80 Switch Count 0

Page Faults 0

Page Reclaims 638

Page Swaps 0

Voluntary Context Switches 4

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 0

85

86 proc contents data=pps.eu_sport_trade;

ERROR: File PPS.EU_SPORT_TRADE.DATA does not exist.

87 run;

NOTE: Statements not processed because of errors noted above.

NOTE: PROCEDURE CONTENTS used (Total process time):

real time 0.00 seconds

user cpu time 0.00 seconds

system cpu time 0.00 seconds

memory 476.75k

OS Memory 20132.00k

Timestamp 07/01/2026 12:55:07 PM

Step Count 81 Switch Count 0

Page Faults 0

Page Reclaims 51

Page Swaps 0

Voluntary Context Switches 4

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 8

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

88

89

90 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

100

---------

ERROS:

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

68

69 ***********************************************************;

70 * LESSON 2, PRACTICE 1 *;

71 * a) Complete the PROC IMPORT step to read *;

72 * EU_SPORT_TRADE.XLSX. Create a SAS table named *;

73 * EU_SPORT_TRADE and replace the table *;

74 * if it exists. *;

75 * b) Modify the PROC CONTENTS code to display the *;

76 * descriptor portion of the EU_SPORT_TRADE table. *;

77 * Submit the program, and then view the output data *;

78 * and the results. *;

79 ***********************************************************;

80 libname pps "/home/u63717210/EPG1V2/practices";

NOTE: Libref PPS was successfully assigned as follows:

Engine: V9

Physical Name: /home/u63717210/EPG1V2/practices

81 proc import Datafile= "pps/p102p01.sas/eu_sport_trade.xlsx"

82 DBMS=xlsx

83 out=pps.eu_sport_trade replace;

84 run;

ERROR: Physical file does not exist, /pbr/biconfig/940/Lev1/SASApp/pps/p102p01.sas/eu_sport_trade.

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

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.00 seconds

user cpu time 0.00 seconds

system cpu time 0.00 seconds

memory 2657.62k

OS Memory 22260.00k

Timestamp 07/01/2026 12:55:07 PM

Step Count 80 Switch Count 0

Page Faults 0

Page Reclaims 638

Page Swaps 0

Voluntary Context Switches 4

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 0

85

86 proc contents data=pps.eu_sport_trade;

ERROR: File PPS.EU_SPORT_TRADE.DATA does not exist.

87 run;

NOTE: Statements not processed because of errors noted above.

NOTE: PROCEDURE CONTENTS used (Total process time):

real time 0.00 seconds

user cpu time 0.00 seconds

system cpu time 0.00 seconds

memory 476.75k

OS Memory 20132.00k

Timestamp 07/01/2026 12:55:07 PM

Step Count 81 Switch Count 0

Page Faults 0

Page Reclaims 51

Page Swaps 0

Voluntary Context Switches 4

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 8

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

88

89

90 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

100

---------------------------------

Notes:

Thank you for your time for comment.

[Tom]

Look very carefully at the PATTERN of the two path names you used:

"/home/u63717210/EPG1V2/practices"
"pps/p102p01.sas/eu_sport_trade.xlsx"

What differences do you see:

Spoiler

The first one starts with /, the root node of the Unix file system, and the second one does not. 

That means that the operating system will treat the first one as an ABSOLUTE (or complete) path and the second one as a RELATIVE path.  When you use a RELATIVE pathname the operating system starts at the current working directory and begins evaluating the path.  So what file it is looking for depends on the setting of the current working directory.

[mkSAS1]

Thank you so much for your comment.

I regret not to ask directly.

My code was initially as like this:

proc import Datafile= " /home/u63717210/EPG1V2/practices/p102p01.sas/eu_sport_trade.xlsx"

Here error happened because "practices" is over 8 characters.  In this situation, how I can circumvent the 9 characters of practice to avoid errors?

Thank you again for your generous comment.

[Tom]

The code you shared does not (and should not) get any errors about something being longer than 8 characters. 

Instead it shows that the file you tried to IMPORT from does not exist because you told SAS to look for it in the wrong place.

ERROR: Physical file does not exist, /pbr/biconfig/940/Lev1/SASApp/pps/p102p01.sas/eu_sport_trade.

Perhaps you tried to make a LIBREF using a name that was longer than 8 characters?  The libref you use in a LIBNAME statement has to be a valid SAS name between one an eight characters.  Its value does not have to match the name of the directory that it points to.  So if you want to make a libref pointing to that practices folder you could call it PRACTICE or FRED or SAM or any other valid SAS name (that is not already being used from some other libref.

libname fred "/home/u63717210/EPG1V2/practices/";
proc import out=fred.myds ...

[mkSAS1]

Thank you for the comment.

The code changed to the following:

libname pract xlsx "/home/u63717210/EPG1V2/practices/p102p01.sas/eu_sport_trade.xlsx";
proc import Datafile=pract
dbms=xlsx
out=eu_sport_trade replace;
run;
proc contents data=eu_sport_trade;
run;

Log:

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

68

69 libname pract xlsx "/home/u63717210/EPG1V2/practices/p102p01.sas/eu_sport_trade.xlsx";

NOTE: Libref PRACT was successfully assigned as follows:

Engine: XLSX

Physical Name: /home/u63717210/EPG1V2/practices/p102p01.sas/eu_sport_trade.xlsx

70 proc import Datafile=pract

71 dbms=xlsx

72 out=eu_sport_trade replace;

73 run;

ERROR: Physical file does not exist, /pbr/biconfig/940/Lev1/SASApp/PRACT.

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

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.00 seconds

user cpu time 0.00 seconds

system cpu time 0.00 seconds

memory 2651.25k

OS Memory 25812.00k

Timestamp 07/03/2026 08:47:36 PM

Step Count 353 Switch Count 0

Page Faults 0

Page Reclaims 637

Page Swaps 0

Voluntary Context Switches 0

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 0

74 proc contents data=eu_sport_trade;

ERROR: File WORK.EU_SPORT_TRADE.DATA does not exist.

75 run;

NOTE: Statements not processed because of errors noted above.

NOTE: PROCEDURE CONTENTS used (Total process time):

real time 0.00 seconds

user cpu time 0.00 seconds

system cpu time 0.00 seconds

memory 522.18k

OS Memory 23684.00k

Timestamp 07/03/2026 08:47:36 PM

Step Count 354 Switch Count 0

Page Faults 0

Page Reclaims 50

Page Swaps 0

Voluntary Context Switches 0

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 8

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

76

77

78 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

88

------------

I believe that I am missing something that I am not able to figure it out.

I appreciate your comment.

Thank you.

[Kurt_Bremser]

You tried to use a library reference (libname) as infile in PROC IMPORT, which cannot work.
Either use a worksheet like a SAS dataset in your Excel library, or use the correct physical filename in the DATAFILE= option of the PROC IMPORT statement.

[Tom]

PROC IMPORT reads from files, not from libraries.  If you don't want to include the actual filename in the PROC IMPORT code then use a FILENAME statement to create a FILEREF.

filename pract "/home/u63717210/EPG1V2/practices/p102p01.sas/eu_sport_trade.xlsx";
proc import
  datafile=pract
  dbms=xlsx
  out=eu_sport_trade replace
;
run;
proc contents data=eu_sport_trade;
run;

If you want to have SAS make the workbook look like a library by using a LIBNAME statement with the XLSX engine then there is no need to use PROC IMPORT.  Instead if you know the name of the worksheet then you can just reference it directly. So if the sheet is named eu_sport_trade then just use that with the LIBREF that you created to reference the data.

libname pract xlsx "/home/u63717210/EPG1V2/practices/p102p01.sas/eu_sport_trade.xlsx";
proc contents data=pract.eu_sport_trade;
run;

If you don't know what the sheets are named then you might try just copying all of the sheets into datasets in the WORK library.

libname pract xlsx "/home/u63717210/EPG1V2/practices/p102p01.sas/eu_sport_trade.xlsx";
proc copy inlib=pract outlib=work;
run;

 After you see the names in the SAS log you will know what dataset name to use in your PROC CONTENTS code.

[ballardw]

One caution with using Excel files as libraries to read data into SAS: They must be clean.

That means column headings only one cell starting on the first row, all the data in a column the same, i.e. numeric with the same type (all dates, all currency, all simple numbers, no mixing) or character. If the Excel is actually more of a report appearance then likely the library approach will lead to much frustration.

If the Excel has two or more column headings then the column will likely be treated as character. If there are one or more blank cells above the headings you will likely again have the column treated a character (and possibly records with missing values). 

At one point in my working career I would say that nearly 20 percent of my work time was cleaning up Excel "data" that the creators could not, or would not, keep consistent in any structure. When the Excel library was introduced in SAS I had one project that it seemed a reasonable solution to the read the frequent files provided. Lasted 3 weeks before the first file change.