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

Reply
Occasional Contributor
Posts: 6

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

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?

Super User
Posts: 19,805

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

Posted in reply to drjingjingliu

What did you try?

Occasional Contributor
Posts: 6

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

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.

New Contributor
Posts: 2

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

Posted in reply to drjingjingliu


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

47        

48         PROC IMPORT OUT= foo.addicts

49                     DATAFILE= "&path/addicts.xls"

50                     DBMS=xls REPLACE;

51          *    RANGE="addicts";

52              GETNAMES=YES;

53        

54             MIXED=NO;

55          *    SCANTEXT=YES;

56          *    USEDATE=YES;

57          *    SCANTIME=YES;

58        

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

Occasional Contributor
Posts: 6

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

thank you so very much! This is definitely helpful!

New Contributor
Posts: 2

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

Posted in reply to drjingjingliu

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.

Occasional Contributor
Posts: 6

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

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!

Occasional Contributor
Posts: 6

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

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.

New Contributor
Posts: 2

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

Posted in reply to drjingjingliu

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

Super User
Posts: 19,805

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

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

New Contributor
Posts: 2

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

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

Ask a Question
Discussion stats
  • 10 replies
  • 4983 views
  • 2 likes
  • 4 in conversation