Unable to import XLSX

[DavidBrown]

Using SAS University Edition and SAS Studio. Following the SAS Certified Specialist Prep Guide instructions, I am attempting to import a practice XLSX file they provided using the code they provided. Here is the sample code:

options validvarname=v7;                                  /*#1*/
proc import datafile='C:\Users\Student1\cert\boots.xlsx'  /*#2*/
  dbms=xlsx
  out=work.bootsales
  replace;
  sheet=boot;                                             /*#3*/
  getnames=yes;                                           /*#4*/
run;
proc contents data=bootsales;                             /*#5*/
run;
proc print data=bootsales;
run;

However, when I change the path to my SASUniversityEdition folder and run the code, I get the following error. 

ERROR: Physical file does not exist, 
        /opt/sasinside/SASConfig/Lev1/SASApp/C:\SASUniversityEdition\myfolders\base-guide-practice-data\cert\/boots

This is the code that generates the error. The Excel file exists. The sheet name is correct. It's in the correct folder. I can even see the XLSX file in the MyFolder: base-guide-practice-data folder, and can open it from there. 

options validvarname=v7;                                  
proc import datafile= 'C:\SASUniversityEdition\myfolders\base-guide-practice-data\cert\boots.xlsx' 
  dbms=xlsx
  out=work.bootsales
  replace;
  sheet=boot;                                             
  getnames=yes;                                          
run;
proc contents data=bootsales;                            
run;
proc print data=bootsales;
run;

The file exists on my C: drive and in SAS Studio.

Why does it say it doesn't exist? 

[Tom · Accepted solution]

Because the virtual machine CANNOT see your C: drive. It can only see the folder you shared with it. AND the name it uses for that folder is 

/folders/myfolders/

https://support.sas.com/software/products/university-edition/faq/shared_folder_whatis.htm

[DavidBrown]

@Tom Thank you for that information. This is extremely frustrating then, as I am following the Prep Guide instructions and it says zilch about UE. It make no sense to "import" the xlsx file from MyFolders in SAS Studio, as it's already apparently in there. The prep guide seems to have many examples that don't work because of this. 

SAS recommends using UE or the On-Demand to learn the software, but then provides a Prep Guide rife with examples that won't work in those environments, or at least as they have them written out. One of the many head scratchers with SAS. 

[Tom]

You are probably using the wrong prep guide then. My understanding is there are different guides for different environments.

[Tom]

It make no sense to "import" the xlsx file from MyFolders in SAS Studio, as it's already apparently in there. 

Sounds like you don't yet understand what PROC IMPORT does. It has NOTHING to do with moving files between folders or servers.  It is about converting files from non-SAS formats into SAS datasets.

[DavidBrown]

@Tom Actually I do get what IMPORT does, but the point of the exercise was the importing data from an EXTERNAL source. Hence, the inclusion of a path with "C:". Thus, my head scratching as to why this didn't work, when I followed the instructions. I know it's not "moving", but rather reading and creating a SAS Data Set from the external file. 

Per the Prep Guide:

The IMPORT procedure reads data from an external data source and writes it to a SAS data set. You can import structured and unstructured data using PROC IMPORT. You can import delimited files (blank, comma, or tab) along with Microsoft Excel files. If you are using SAS 9.4, then you can import JMP 7 or later files as well. 

Per the Prep Guide:

Example: Importing an Excel File with an XLSX Extension

This example imports an Excel file and creates a temporary SAS data set, Work.BootSales.

options validvarname=v7;                                  /*#1*/
proc import datafile='C:\Users\Student1\cert\boots.xlsx'  /*#2*/
  dbms=xlsx
  out=work.bootsales
  replace;
  sheet=boot;                                             /*#3*/
  getnames=yes;                                           /*#4*/
run;
proc contents data=bootsales;                             /*#5*/
run;
proc print data=bootsales;
run;

Thanks again. Hopefully, I don't sound snarky. Just finding the SAS learning curve fairly steep, and the way the guides and documentation are written seem to make it steeper. 

[Tom]

PROC IMPORT mainly just reads from file based "sources".  The only part of that can handle anything remote would be if you were using one of the DBMS engines that accesses files using the SAS PC Files Server. 

To read from real database sources you will want to learn to use SAS/Access features like LIBNAME engines or the CONNECT statement in PROC SQL.

[Tom]

Like I said you are using the prep guide for someone that is running PC SAS, or at least is running SAS on a Windows server.