topic Re: How to import excel file from a Mac to SAS studio? Please help in SAS Programming

How to import excel file from a Mac to SAS studio? Please help

drjingjingliu — Thu, 17 Jul 2014 21:49:17 GMT

Hello, so I'm using SAS studio on a Mac right now, and looks like I can't find the right way to import my excel file from my Mac into SAS studio, is there anyone who can help solving this issue?

Re: How to import excel file from a Mac to SAS studio? Please help

Reeza — Thu, 17 Jul 2014 22:26:46 GMT

What did you try?

Re: How to import excel file from a Mac to SAS studio? Please help

KenB — Fri, 18 Jul 2014 00:42:35 GMT

My first time with this, I would get a 'spinning wheel' in a RUNNING window that indicated that something was occurring but nothing ever did.

It finally works after restarting the VirtualBox SAS engine once the file path was set up.

SAS appears to want the files in the MyFolders area. For me to set that up, I have under SASUniversityEdition a 'myfolders' folder

/SASUniversityEdition/myfolders/

After starting the virtual box machine, SAS created a new folder 'sysuser.v94'

/SASUniversityEdition/myfolders/sysuser.v94/

It is in this folder (on the Mac), I placed the Excel file.

The PROC IMPORT looks like:

PROC IMPORT OUT= work.addicts

DATAFILE= "/folders/myfolders/sasuser.v94/addicts.xls"

DBMS=xls REPLACE;

RANGE="addicts";

GETNAMES=YES;

MIXED=NO;

* SCANTEXT=YES;

* USEDATE=YES;

* SCANTIME=YES;

RUN;/** Import an XLS file. **/

This version of SAS does not like the keyword EXCEL. Use XLS.

It would appear that you need to create the myfolder directory structure before starting the virtual machine. Refresh within SAS does not appear to work correctly. I don't believe that you are restricted to using the sub-folder sasuser.v94.

Re: How to import excel file from a Mac to SAS studio? Please help

drjingjingliu — Fri, 18 Jul 2014 00:57:41 GMT

Thank you so very much for your detailed explanation, very useful. Based on what you've just said, I have a couple of questions and hopefully can be addressed by you soon.

1) You said to place my excel file in the sasuser.v94 folder in my mac, but when I open the folder 'SASUniversityEdition' and 'my folders' in it, I actually found it is empty inside, so do I have to create a 'sasuser.v94' in this folder by myself and then drag the excel file in there and then restart the virtual box?

2) After set up the folder, what would the libname and path looks like? is it:

%let path=/folders/myfolders/sasuser.94;

libname addicts "&path"; /*is it correct?*/

Thanks again!

Re: How to import excel file from a Mac to SAS studio? Please help

drjingjingliu — Fri, 18 Jul 2014 01:32:41 GMT

Hi Ken, so I just ran my code as:

PROC IMPORT OUT=work.saspractice

DATAFILE="/folders/myfolders/sasuser.v94/saspractice.xls" DBMS=xls REPLACE;

RANGE="addicts";

GETNAMES=YES;

MIXED=NO;

* SCANTEXT=YES;

* USEDATE=YES;

* SCANTIME=YES;

RUN;

However, it gave me error like: ERROR: Physical file does not exist, /folders/myfolders/sasuser.v94/saspractice.xls.

Can you tell where went wrong here??... Really confused. Thanks.

Re: How to import excel file from a Mac to SAS studio? Please help

drjingjingliu — Fri, 18 Jul 2014 01:33:33 GMT

Hi Reeza, here is what I tried and what I got:

PROC IMPORT OUT=work.saspractice

DATAFILE="/folders/myfolders/sasuser.v94/saspractice.xls" DBMS=xls REPLACE;

RANGE="addicts";

GETNAMES=YES;

MIXED=NO;

* SCANTEXT=YES;

* USEDATE=YES;

* SCANTIME=YES;

RUN;

However, it gave me error like: ERROR: Physical file does not exist, /folders/myfolders/sasuser.v94/saspractice.xls.

Very confused here for the excel file import...... Thanks.

Re: How to import excel file from a Mac to SAS studio? Please help

KenB — Fri, 18 Jul 2014 07:05:35 GMT

Hi Dr,

I can understand your frustration. You might try removing the RANGE keyword for the import as well (it names the worksheet in the file).

On my Mac system, I can go to a 'Terminal' window and type/execute the following bolded command and I see 3 files in the folder listed.

$ ls /SASUniversityEdition/myfolders/sasuser.v94/

HM1.sas Program 1.sas addicts.xls

So I can see my Excel file of 'addicts.xls' plus other files. This is what I also see in the left pane of the SAS window under My Folders.

I created the /SASUniversityEdition folder on the top level of my system (usually 'Macintosh HD') as well as the 'myfolders' folder underneath that. After doing that, the next time I signed into SAS, the 'sasuser.v94' folder was created for me. I would not believe that it would make much difference if I had created (but ownership of the folder might be different).

Your /SASUniversityEdition folder may be under a different folder (such as your username on the Mac). Also make sure your shared folder setup allows FULL access (under Virtual Box Devices).

As to the macro. This syntax works for me:

%let path=/folders/myfolders/sasuser.v94 ;

libname foo "&path" ;

PROC IMPORT OUT= work.addicts

DATAFILE= "&path./addicts.xls"

DBMS=xls REPLACE;

* RANGE="addicts";

GETNAMES=YES;

MIXED=NO;

* SCANTEXT=YES;

* USEDATE=YES;

* SCANTIME=YES;

RUN;/** Import an XLS file. **/

45 %let path=/folders/myfolders/sasuser.v94 ;

46 libname foo "&path" ;

NOTE: Libref FOO was successfully assigned as follows:

Engine: V9

Physical Name: /folders/myfolders/sasuser.v94

48 PROC IMPORT OUT= foo.addicts

49 DATAFILE= "&path/addicts.xls"

50 DBMS=xls REPLACE;

51 * RANGE="addicts";

52 GETNAMES=YES;

54 MIXED=NO;

55 * SCANTEXT=YES;

56 * USEDATE=YES;

57 * SCANTIME=YES;

59 RUN;

NOTE: The import data set has 238 observations and 6 variables.

NOTE: FOO.ADDICTS data set was successfully created.

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.03 seconds

cpu time 0.03 seconds

Re: How to import excel file from a Mac to SAS studio? Please help

drjingjingliu — Fri, 18 Jul 2014 14:52:10 GMT

thank you so very much! This is definitely helpful!

Re: How to import excel file from a Mac to SAS studio? Please help

Reeza — Fri, 18 Jul 2014 15:03:00 GMT

You can set up your own folders/paths as explained here as well:

Re: How to import excel file from a Mac to SAS studio? Please help

tranbadung200 — Fri, 28 Aug 2015 19:54:16 GMT

This is a perfect explanation for this issue. Thank you very much

Re: How to import excel file from a Mac to SAS studio? Please help

tranbadung200 — Fri, 28 Aug 2015 19:57:14 GMT

This is pretty simple to fix. Here is how I look at it

Do you have the file "saspractice.xls" under the path folders/myfolders/sasuser.v94? If you don't, then that is the error. You should have upload that file from you computer into this path, then the error will disappear.

I hope this help

Re: How to import excel file from a Mac to SAS studio? Please help

aayeshasingh — Wed, 20 May 2020 11:59:57 GMT

can we import it by using libname statement??