Learning SAS? Welcome to the exclusive online community for all SAS learners.

I am not able to access my excel file using SAS studio and cant create library either.

Reply
Occasional Contributor
Posts: 16

I am not able to access my excel file using SAS studio and cant create library either.

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

42 ;

43 /** Import an XLS file. **/

44 

45 PROC IMPORT DATAFILE="C:/SASUniversityEdition/myfolders/sasuser.v94/myfolder/newdata1.xlsx"

46 OUT=WORK.MYEXCEL

47 DBMS=XLS

48 REPLACE;

49 RUN;

ERROR: Physical file does not exist,

  /opt/sasinside/SASConfig/Lev1/SASApp/C:/SASUniversityEdition/myfolders/sasuser.v94/myfolder/newdata1.xlsx.

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

NOTE: PROCEDURE IMPORT used (Total process time):

  real time 0.01 seconds

  cpu time 0.02 seconds

  

50 

51 /** Print the results. **/

52 

53 PROC PRINT DATA=WORK.MYEXCEL; RUN;

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

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.01 seconds

  

54 ;

55 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

65 ;

Valued Guide
Posts: 3,208

Re: I am not able to access my excel file using SAS studio and cant create library either.

With the analyticsu you are using a Unix virtual machine.

Every naming to a physical file should follow the Unix naming conventions.

Check the sas Unix companion.

As excel is not running in Unix of course you cannot access that. The xlsx files however should be able to be processed. In your virtual machine you can define the connection as documented

---->-- ja karman --<-----
Occasional Contributor
Posts: 16

Re: I am not able to access my excel file using SAS studio and cant create library either.

Can you please tell me how to create a library and then put my excel file in it for further operations.

New Contributor
Posts: 2

Re: I am not able to access my excel file using SAS studio and cant create library either.

I had the same issues but finally got it to work by making sure to reference the shared folder ie./folders/myfolders/sasuser.v94/myfolder/newdata1.xlsx

Learner
Posts: 1

Re: I am not able to access my excel file using SAS studio and cant create library either.

thank you very much my issue is solved now

N/A
Posts: 1

Re: I am not able to access my excel file using SAS studio and cant create library either.

Are you using the SAS University Edition copy?   I am also getting the same issue.

SAS Super FREQ
Posts: 8,743

Re: I am not able to access my excel file using SAS studio and cant create library either.

Hi:

 

  Please review the attached screen shots carefully. First note that I have an Excel workbook, entitled NEWCLASS.XLSX in a folder on my C: drive called C:\SASUniversityEdition\myfolders\all_excel\newclass.xlsx -- however, since the SAS University Edition is a UNIX virtual machine, I cannot use the C: drive location for the code. Instead, I must point to the file using this shared folder name. (If you do not know how to set up a shared folder, you MUST read the documentation for your virtual machine software or you must watch one of the installation/configuration videos.)

       

  The Virtual Machine/Unix shared folder name for my Excel file NEWCLASS.XLS is: '/folders/myfolders/all_excel/newclass.xlsx' in my PROC IMPORT step. Note how the slashes are Unix slashes (not Windows slashes) and the location is NOT (NOT, NOT, NOT) a Windows folder location. You do not put your Excel file into a SAS library. You must put your Excel file into a shared folder and then run an IMPORT step to IMPORT your Excel file into SAS dataset format. And, when you use PROC IMPORT, you must use the form of DBMS= that represents what type of file you have. For example, if you have a proprietary file named .XLS, then DBMS= value would be DBMS=XLS; if you have an XLSX file then, as shown in my code, the DBMS= value would be DBMS=XLSX.

     

  There are 6 screen shots, that show the code I used to successfully import the NEWCLASS.XLSX file into a SAS dataset name NEWCLASS in the PERM library. I prefer to keep my different data files separate from each other -- that way, it is always clear to me where and what folder I am READING FROM and where and what library/folder I am WRITING TO. Therefore, the location of my imported SAS dataset is a folder called: C:\SASUniversityEdition\myfolders\all_SAS_data -- note that again, this is a C: drive location in my shared folder directory. So in my code, the appropriate LIBNAME statement to "plug SAS into" this location, as a library definition is:

    

libname perm '/folders/myfolders/all_SAS_data';

      

  The "nickname" or LIBREF of PERM is the first level name that I've assigned to my location. Note again that the slashes are all Unix slashes and instead of a C:\ drive location, I use my Unix virtual machine location of /folders/myfolders/all_SAS_data. There is nothing special about the name PERM. It conforms to the SAS naming convention (must be 1-8 characters, start with a letter or underscore and continue with letters, numbers or underscores). I could have used a nickname of WOMBAT or FRED or MYDATA. The fact is that the "nickname" or LIBREF must be established with a LIBNAME statement every time I start SAS -- sort of like plugging in your smartphone -- if your smartphone needs power you have to plug it in; if your SAS program needs SAS data, then you have to "plug in" or point to the SAS library location where your data lives. Without a LIBNAME statement, SAS doesn't know where to look for your permanent data files. Once I establish the nickname or libref, then I can refer to my SAS data with a 2 level name, such as PERM.NEWCLASS, WOMBAT.NEWCLASS, FRED.NEWCLASS or MYDATA.NEWCLASS. Whatever nickname I use becomes the first-level or library name in the 2-level name of the permanent dataset.

          

  Of course, you could re-do the IMPORT every time you start SAS and write your SAS imported files into the WORK (temporary) location, but if your data are NOT changing, then this doesn't make sense.

       

  The #7 screen shot shows how to import multiple sheets into 2 separate datasets. And the last #8  screen shot is annotated with these questions shown next to the code you need to write:

1) What nickname are you going to use to write your SAS dataset to a permanent location? (It's OK to skip this step, but if you do, and you use a 2-level name, you may get an error if your LIBNAME is not assigned. Or you can write your dataset to the WORK location by using a 1-level name in your code.)

2) What is the Unix shared folder path where you want to WRITE your imported SAS file?

3) What is the fully qualified Unix shared folder path where you want to READ your Excel file from?

4) What is the 2-level name of the SAS dataset you are importing/creating? If you use WORK.dataset or just a 1-level name, then the file is temporary. If you have a LIBNAME statement from #1, then use the "nickname" for the first part of the 2-level name, for example PERM.NEWCLASS.

5) What is the DBMS= value that you need to use? Hint, look at the file extension of the file you pointed to in #3 -- the most common are DBMS=XLS or DBMS=XLSX or DBMS=CSV.

6) If your workbook has multiple sheets, you will need a PROC IMPORT step for each sheet. You must use the SHEET= option to provide the name of each sheet. See the screen shot #7 with the example.

     

  Note that you ONLY need to do the import 1 time if you save the imported file in a permanent SAS library. That means you have created a permanent SAS dataset in a permanent SAS library. The next time you want to log onto SAS and "plug in" to your data, you ONLY need to submit the correct LIBNAME statement to point to existing SAS datasets.

       

cynthia


_4_SAS_Studio_folder_view_after_running_code.png_6_SAS_Studio_Libraries_pane_shows_new_dataset.png_5_SAS_Studio_log_after_running_code.png_3_SAS_Studio_code_and_folders_explained.png_2_sas_studio_view_BEFORE_running_code.png_8_questions_to_answer_before_you_write_import_code.png_1_Windows_Explorer_view_of_folder_with_xlsx_file.png_7_how_read_multiple_sheets.png
N/A
Posts: 1

Re: I am not able to access my excel file using SAS studio and cant create library either.

Thank you, Cynthia! Great, thorough explanation.

p.s. It worked, by the way.

N/A
Posts: 1

Re: I am not able to access my excel file using SAS studio and cant create library either.

Hi Cynthia,

        Thank You for the detailed explanation. It really helped me even though I am quite new to SAS. Really appreciate your efforts.

Warm Regards,

Nikhil

N/A
Posts: 1

Re: I am not able to access my excel file using SAS studio and cant create library either.

Hi Cynthia,

You helped me so much. Great explanation. Thank you so much.

Warm Regards

José Humberto

Re: I am not able to access my excel file using SAS studio and cant create library either.

Hi Cynthia,

Thanks a lot for the explicit explanation. It worked out pretty well!

Best regards,

Polly

Occasional Contributor
Posts: 6

Re: I am not able to access my excel file using SAS studio and cant create library either.

Thanx a lot Cythia. Its really helpful.I am medical student and working on clinical research projects. Just started working on SAS. Please send some useful link which u think will be helpful to me at your convenience.

SAS Super FREQ
Posts: 8,743

Re: I am not able to access my excel file using SAS studio and cant create library either.


Hi:

  I'm not sure what you would consider a "useful link" -- aside from taking the free Programming 1 and Statistics 1 classes, here are some free tutorials to watch: SAS Tutorials | SAS Training. And, once you know what topics you are interested in, searching on Google, support.sas.com or lexjansen.com have always revealed good resources for me.

cynthia

Occasional Contributor
Posts: 8

Re: I am not able to access my excel file using SAS studio and cant create library either.

Hi Cynthia,

My issue of not being able to proc import an Excel file is slightly different than the one you are addressing in your reply. Here are the specifics.

1. I am using SAS University Edition on HP desktop, Windows 7

2. I have performed all the steps that you are describing in your post such as: I created a folder on the C:\ drive which I shared on the VM. I also followed the instructions in SAS® University Edition: Installation Guide for Windows which say to add NAT (network address translation) which have not been mentioned in your post. I placed an Excel file with the *.XLS extension into the shared folder. When I am in the SAS Studio I can see my Excel file in My Folders. My program contains a statement:

libname mydata '/folders/myfolders';

This statement executes with no errors. The LOG output:

libname mydata '/folders/myfolders';

NOTE: Libref MYDATA was successfully assigned as follows:

  Engine: V9

  Physical Name: /folders/myfolders

But when I run the proc import it does not recognize the Excel file in the '/folders/myfolders' directoryHere is the snippet of my code and LOG error.

libname mydata '/folders/myfolders';

proc import datafile='/folders/myfolders/filename1.xls'

     out=WORK.filename1

     dbms=xls

     replace;

run;

Here is the LOG output:

43 libname mydata '/folders/myfolders';

NOTE: Libref MYDATA was successfully assigned as follows:

  Engine: V9

  Physical Name: /folders/myfolders

44 

45 

46 proc import datafile='/folders/myfolders/filename1.xls'

47 out=WORK.filename1

48 dbms=xls

49 replace;

50 run;

ERROR: Physical file does not exist, /folders/myfolders/filename1.xls.

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

So, my problem is: the shared folders are recognized in the LIBNAME statement but the fully qualified file name is not recognized by PROC IMPORT. I also tried running VM in both modes: the host OS is Windows 7 and Linux with the same outcome.

I'd appreciate any suggestions.

Thank you,

Eager

Super User
Super User
Posts: 6,502

Re: I am not able to access my excel file using SAS studio and cant create library either.

Are you sure you have specified the filename properly.

Try running this little progam that try to make a copy of the SASHELP.CLASS table as a SAS dataset and an XLS file. And then read the XLS file back into a work dataset.

libname xx '/folders/myfolders';

data xx.class; set sashelp.class; run;

proc export data=xx.class file='/folders/myfolders/class.xls' dbms=xls replace;

run;

proc import out=class datafile='/folders/myfolders/class.xls' dbms=xls replace;

run;

proc print data=class;

run;

Ask a Question
Discussion stats