BookmarkSubscribeRSS Feed
drjingjingliu
Calcite | Level 5

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?

11 REPLIES 11
drjingjingliu
Calcite | Level 5

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.

KenB
Calcite | Level 5


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

drjingjingliu
Calcite | Level 5

thank you so very much! This is definitely helpful!

KenB
Calcite | Level 5

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.

drjingjingliu
Calcite | Level 5

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!

drjingjingliu
Calcite | Level 5

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.

tranbadung200
Calcite | Level 5

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

aayeshasingh
Calcite | Level 5

can we import it by using libname statement??

Reeza
Super User

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

tranbadung200
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 11061 views
  • 2 likes
  • 5 in conversation