Using SAS on UNIX to connect to MS Excel / Access Databases files on the LAN using SAS PC Files Server on your system...

Using SAS on UNIX to connect to MS Excel / Access Databases files on the LAN using SAS PC Files Server on your system.

In order to connect to MS Access or MS Excel files on the LAN from UNIX BOX you will need to install and have the SAS PC Files Server running on your system.

Open SAS PC Files Server from Start | Programs | SAS | SAS PC Files Server on your system.

You will also need the IP address of your workstation and the actual name of your LAN drive.
Go to your start menu and select “run.”
Type cmd and press enter.

Type pingand your workstation ID (i.e. Windows Login ID ) and press enter. This will provide you with your IP address or you can use ipconfig to get your system’s ipaddress.

Next type net use and press enter. This will provide you with a list of your LAN drives and their actual names. It is best practice to use the actual name of a server instead of an alias. This will also make your code more portable.

Next you will need to launch SAS on your UNIX BOX.

Now you can use either of the methods below to access the database tables on your LAN drive. You will need to substitute your IP address for the Server and the location of your MS Access or MS Excel file on the LAN for the Path/Database portions of the sample syntax.

1) Libname Method

libname test1 pcfiles server=‘xxx.xx.xxx.xx’

port=zzzz

path="\\LAN-SERVER\DIRPATH\db1.mdb";

run;

reads from an MS ACCESS table (TABLE1) and creates a SAS Data Set (NEW)

data work.new;

set test1.table1;

run;

reads from a SAS Data Set (CLASS) and creates an MS ACCESS table (NEW)

data test1.new;

set sashelp.class;

run;

(You can also run SAS PROC SQL against this Libref.)

2) PROC IMPORT and PROC EXPORT method

reads from an MS ACCESS table (TEST1) and creates a SAS Data Set (NEW)

proc import dbms=accesscs

table=test1 out=work.new;

database="\\LAN-SERVER\DIRPATH\db1.mdb";

server=‘xxx.xx.xxx.xx’;

port=zzzz;

run;

reads from a SAS Data Set (CLASS) and creates an MS ACCESS table (NEW)

proc export dbms=accesscs

data=sashelp.class;

outtable=new;

database="\\LAN-SERVER\DIRPATH\db1.mdb";

server=‘xxx.xx.xxx.xx’;

port=zzzz;

run;

For more information on how to use the PC Files Server please see the SAS Online Docs website at http://support.sas.com/onlinedoc/913/docMainpage.jsp. From this page you can navigate to SAS/ACCESS Software and then to SAS/ACCESS for PC Files: Reference.