topic Import and read xlsx file in SAS in SAS Programming

Import and read xlsx file in SAS

aabbccwyt — Thu, 26 Sep 2019 18:23:13 GMT

Hi. I'm using the SAS Studio University Edition. How can I import an excel file (name it 2019.xlsx) from my local computer to SAS and use a library to read that xlsx file? What would the codes be? Thank you

Re: Import and read xlsx file in SAS

Tom — Thu, 26 Sep 2019 18:36:02 GMT

For SAS to see the file you need to put it into (or under) the folder on your real machine that you shared with the virtual machine where SAS is running. You could just place it there outside of the SAS/Studio interface or use the file upload feature in SAS/Studio interface.

Once you have it there you need to know where it is from SAS's point of view. An easy way to find that is to navigate to the file in SAS/Studio interface and right click on it and select properties. One of the properties is the actual name/path of the file. So copy that and paste it into the code editor window. Use that to make a libname using XLSX engine. Remember that the name you use for the library (the LIBREF) must be a valid SAS name of 8 character or less.

So this program will point the libref MYLIB at the file and then copy all of the sheets out as datasets in the WORK library.

libname mylib xlsx "/folders/myfolders/2009.xslx";
proc copy inlib=mylib out=work;
run;

Re: Import and read xlsx file in SAS

Kurt_Bremser — Thu, 26 Sep 2019 18:42:56 GMT

You should work through the free online Programming 1 course to get a grasp on the basic usage of SAS.

Re: Import and read xlsx file in SAS

aabbccwyt — Thu, 26 Sep 2019 19:32:08 GMT

Thank you so much for replying. Is there is a way not to use the shared folder thing? I'm using Macbook btw.

Re: Import and read xlsx file in SAS

aabbccwyt — Thu, 26 Sep 2019 19:39:29 GMT

Here is what I have.

Re: Import and read xlsx file in SAS

Tom — Thu, 26 Sep 2019 19:43:33 GMT

Looks like the file is there. Are you having any trouble?

Re: Import and read xlsx file in SAS

Kurt_Bremser — Thu, 26 Sep 2019 20:05:47 GMT

@aabbccwyt wrote:

Thank you so much for replying. Is there is a way not to use the shared folder thing? I'm using Macbook btw.

All communication regarding files is done through the shared folder. It is set up explicitly for that.

Re: Import and read xlsx file in SAS

aabbccwyt — Thu, 26 Sep 2019 20:26:03 GMT

Thank you so much!