topic Re: Importing excel data from a single work sheet in New SAS User

Importing excel data from a single work sheet

potato — Sat, 30 May 2020 07:27:01 GMT

Hi, I am stuck on level 1 practise and can not figure out how to solve it. This is the code that should be correct, but I still get the following errors. ( ERROR: Physical file does not exist, /pbr/biconfig/940/Lev1/SASApp/home/userid/EPG194/data//eu_sport_trade. ) and (ERROR: File WORK.EU_SPORT_TRADE.DATA does not exist. )

proc import datafile="home/userid/EPG194/data/eu_sport_trade.xlsx"
dbms=xlsx
out=eu_sport_trade
replace;
run;

proc contents data=eu_sport_trade;
run;

Re: Importing excel data from a single work sheet

Jagadishkatam — Sat, 30 May 2020 07:30:35 GMT

did you try to use the full path of the file like

/pbr/biconfig/940/Lev1/SASApp/home/userid/EPG194/data//eu_sport_trade.xlsx

Re: Importing excel data from a single work sheet

potato — Sat, 30 May 2020 07:33:26 GMT

I tried the code and got the following errors again. ERROR: Physical file does not exist, /pbr/biconfig/940/Lev1/SASApp/home/userid/EPG194/data///eu_sport_trade

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

Re: Importing excel data from a single work sheet

Kurt_Bremser — Sat, 30 May 2020 08:14:33 GMT

On UNIX, use absolute path names starting at the root directory. You missed to use a forward slash at the beginning of your file name:

"/home/userid/EPG194/data/eu_sport_trade.xlsx"

Re: Importing excel data from a single work sheet

pjbyrne92 — Mon, 30 Nov 2020 14:07:48 GMT

Hi,

I used the below code, whereby it gave me the same 2 errors as you upon initially running, but then I highlighted the whole code and clicked "format code", ran it again and it worked!

proc import
datafile="/home/u50224767/EPG194/activities/data/eu_sport_trade.xlsx"
dbms=xlsx out=eu_sport_trade replace;
run;

proc contents data=eu_sport_trade;
run;

Re: Importing excel data from a single work sheet

Akhil123 — Thu, 16 Sep 2021 20:22:56 GMT

How many variables are in the eu_sport_trade table?

Re: Importing excel data from a single work sheet

Cynthia_sas — Thu, 16 Sep 2021 20:50:25 GMT

Hi:

I believe that you have the wrong path. If you are starting with the home folder, you need a leading slash also, you MUST put YOUR UserID into the path (not the string "userid":

/home/<yourUserID>/EPG194/data/eu_sport_trade.xlsx

So, if your UserID is u1234567, and you have instructions to use the class folder EPG194, then the path would be:

/home/u1234567/EPG194/data/eu_sport_trade.xlsx

Or, if your user id is u9876543 and you are taking the most current version of the class with a folder of EPG1V2, then the path would be:

/home/u9876543/EPG1V2/data/eu_sport_trade.xlsx

The bottom line is that there is a never-fail method for finding the correct path to use. Right-click on Files (Home) top node in the Server Files and Folders path and choose Properties. In the Location field on the Properties window, you'll see that full and correct path. Next click to see the Properties on your course data subfolder and you'll see THAT full and correct path. Finally, expand the data subfolder and look for the XLSX file. Right click and choose Properties to see the full and correct path for the XLSX file. The nice thing about this method is that you can highlight the correct path in the Location field of the Properties window and just copy and paste the path into the code location where you need it.

Cynthia

Re: Importing excel data from a single work sheet

Alexo2025 — Sat, 17 Dec 2022 14:09:16 GMT

Great thanks for the solution. It helps me also!