Desktop productivity for business analysts and programmers

import/export code for sas eg

Reply
Contributor
Posts: 39

import/export code for sas eg

i have the following:

Windows 7 Enterprise 64 bit

SAS EG 6.1 64 bit

Microsoft Office (Excel) Professional 2013 32 bit

Everything I seem to be reading says that you can't directly import/export excel files from the computer if the bitness doesn't match...is this correct?  I tried installing a PC Files Server and that did not work with EG.  Am I missing something?

***hopefully I can clarify.  I want to write a program within EG so that I can import multiple excel files from the same folder on my desktop, then perform queries on them in SAS, and then export them back into new (individual) excel files on my computer, versus doing everything manually through file->import (and one at a time)***

Message was edited by: Alayna Roberts

Super User
Posts: 3,233

Re: import/export code for sas eg

The IMPORT procedure is a good place to start for importing Excel files - have you tried this? However this runs on your SAS server - are you using a local (SAS on your desktop) or remote SAS server?

This is what your IMPORT step might look like if running on Windows:

PROC IMPORT OUT = MySheet

        FILE = "\\MyServer\MyFolder\MyExcel.xls"

        DBMS = XLS

        REPLACE;

  sheet = "MySheet";

  GETNAMES = YES;

RUN;

Contributor
Posts: 39

Re: import/export code for sas eg

I tried this:

PROC IMPORT OUT = THD_Data

          FILE = "C:\Users\ENGACR\Desktop\2014_Q3_THD_Test\Car_1.xlsx"

          DBMS = XLSX

          REPLACE;

  sheet = "Sheet1";

  GETNAMES = YES;

  RUN;

where the file is stored on my desktop.  I keep getting this error:

   ERROR: Physical file does not exist,C:\Users\ENGACR\Desktop\2014_Q3_THD_Test\Car_1.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.01 seconds

Super User
Posts: 7,400

Re: import/export code for sas eg

Enterprise Guide uses a SAS workspace server (EG is just a frontend), which can be installed locally (on your PC) or on a remote server.

So the first question is, where is your SAS actually installed and on which operating system, and how can the systems communicate (if separated). This is where the PC Files Server comes into play.

Even more important is the fundamental question of "what do you want to achieve", BI-wise. Excel is not suited for statistical analyses or business intelligence AT ALL and should be avoided by all means.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 39

Re: import/export code for sas eg

How do I check to find out if SAS is installed locally or on a remote server?  Physically I go to Start->All Programs ->SAS -> SAS Enterprise Guide 6.1

As far as using excel, it is ONLY being used to create graphs once all the data has been processed/queried in SAS.  For my work's purposes that's what they prefer in power points; graphs from excel (apparently they're not 'pretty' enough out of SAS :smileysilly: )

I just want to know how to import/export multiple files and use SAS EG in both manners of using the GUI aspect and applying my knowledge of coding (i.e. code macros, but then use the built in 'query' function)

Super User
Posts: 7,400

Re: import/export code for sas eg

You only start Enterprise Guide, which is NOT the SAS system.

Open the server list in Enterprise Guide and look which server(s) is/are listed. "Local" is usually a SAS installation on your desktop, while "SASApp" is the default name for a remote SAS server. Right-clicking on a server and selecting properties will show the server's DNS name.

The fact that the SAS workspace serve can't find your file indicates to me that SAS runs on a remote server, and the fact that it does use your path name as an absolute one without prepending it's own working path shows that it runs on Windows.

So you most probably need to find a way to make your Excel files visible to the SAS server via shared drives.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 5 replies
  • 2904 views
  • 0 likes
  • 3 in conversation