topic Re: Accessing SAS/UNIX files using MS ACCESS in SAS Procedures

Accessing SAS/UNIX files using MS ACCESS

BKP — Mon, 10 Oct 2016 16:29:24 GMT

Hello, I am a SAS novice and learning SAS on my own. Here is what I'm trying to do:

I have SAS installed on UNIX. I use MS ACCESS to produce GUI based reports. I have few large files in SAS/UNIX. Is it possible to run query in SAS/UNIX files and produce report in MS ACCESS? I don’t want to import huge files from SAS/UNIX to MS ACCESS.

Re: Accessing SAS/UNIX files using MS ACCESS

ChrisHemedinger — Mon, 10 Oct 2016 16:53:34 GMT

If you have a SAS/SHARE server set up, you can use the SAS ODBC driver:

http://support.sas.com/software/products/odbc/

If you have SAS Integration Technologies (for use with SAS Enterprise Guide, for example), you can use the SAS IOM OLE DB provider. You'll have to specify a connection string that includes your SAS Workspace host, port, and credentials.

Be sure to apply proper filters when you access (SQL or WHERE clause) so that you don't pull that huge data into your MS Access session.

Re: Accessing SAS/UNIX files using MS ACCESS

BKP — Mon, 10 Oct 2016 17:52:34 GMT

Is it possible to run query against SAS files stored on UNIX without importing files to MS Access?

Re: Accessing SAS/UNIX files using MS ACCESS

SASKiwi — Mon, 10 Oct 2016 18:55:26 GMT

One approach might be to do all of your big volume data processing in SAS, producing summary SAS tables that you then export to Access. If you have SAS/ACCESS Interface to PC Files you can export to MS Access instead of importing from it.

Re: Accessing SAS/UNIX files using MS ACCESS

rogerjdeangelis — Tue, 11 Oct 2016 00:33:41 GMT

You should be able to do this with the IML interface to R

You will need a prebult access database to write into.
Microsoft changes the structure of access so R works better
if you supply the access database.

You can find a sample access databases at
C:\Program Files\sashome\SASFoundation\9.4\access\sasmisc


* I have 64bit SAS 9.4M2 on Windows and 64 bit Office
* there are options for all combinations of 32 and 64bit R or/and Office
* Should work almost everywhere (Windows/Unicies/Mac OS but not Z/OS or VMS)



HAVE  Subset of SASHEL.CLASS
============================

SASHELP.CLASS

Up to 40 obs SD1.CLASS total obs=19

Obs    NAME       AGE    HEIGHT    WEIGHT

  1    Alfred      14     69.0      112.5
  2    Alice       13     56.5       84.0
  3    Barbara     13     65.3       98.0
  4    Carol       14     62.8      102.5
  5    Henry       14     63.5      102.5
  6    James       12     57.3       83.0
  7    Jane        12     59.8       84.5

WANT ( Coefficient and residual reports in MS Access)
===============================================

COEFFICIENTS

             names.cof.         cof
(Intercept) (Intercept) -141.223763
AGE                 AGE    1.278393
HEIGHT           HEIGHT    3.597027

RESIDUALS

      NAME SEX AGE HEIGHT WEIGHT        RESID       FIT
1   Alfred   M  14   69.0  112.5 -12.36856100 124.86856
2    Alice   F  13   56.5   84.0   5.37266290  78.62734
3  Barbara   F  13   65.3   98.0 -12.28117040 110.28117
4    Carol   F  14   62.8  102.5  -0.06699663 102.56700
5    Henry   M  14   63.5  102.5  -2.58491519 105.08492
6    James   M  12   57.3   83.0   2.77343421  80.22657
7     Jane   F  12   59.8   84.5  -4.71913207  89.21913
8    Janet   F  15   62.5  112.5   9.73371881 102.76628
9  Jeffrey   M  13   62.5   84.0 -16.20949616 100.20950
10    John   M  12   59.0   99.5  13.15848914  86.34151
11   Joyce   F  11   51.3   50.5  -6.86601421  57.36601
12    Judy   F  14   64.3   90.0 -17.96253640 107.96254
13  Louise   F  12   56.3   77.0   0.37046072  76.62954
14    Mary   F  15   66.5  112.0  -5.15438724 117.15439
15  Philip   M  16   72.0  150.0  11.78357444 138.21643
16  Robert   M  12   64.8  128.0  20.79573537 107.20426
17  Ronald   M  15   67.0  133.0  14.04709951 118.95290
18  Thomas   M  11   57.5   85.0   5.33242142  79.66758
19 William   M  15   66.5  112.0  -5.15438724 117.15439


SOLUTION

options validvarname=upcase;
libname sd1 "d:/sd1";
data sd1.class;
  set sashelp.class(keep=name age weight height);
run;quit;

%utl_submit_r64('
library(haven);
library(RODBC);
library(Zelig);
myDB<-odbcDriverConnect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=d:/mdb/demo.accdb");
class<-read_sas("d:/sd1/class.sas7bdat");
z.o1 <- aov(WEIGHT ~ AGE + HEIGHT,data=class);
cof<-z.o1$coefficients;
coef=data.frame(names(cof),cof);
coef;
RESID<-z.o1$residuals;
FIT<-z.o1$fitted.values;
residual<-cbind(class,RESID,FIT);
residual;
sqlSave(myDB,residual,rownames=FALSE);
sqlSave(myDB,coef,rownames=FALSE);
close(myDB);
');

Re: Accessing SAS/UNIX files using MS ACCESS

ChrisHemedinger — Tue, 11 Oct 2016 01:28:48 GMT

You can apply a filter as a query on the "command" you send via OLE DB. This SAS Note has some VB Script examples. These might be overkill for what you're looking for.

It sounds like you need to access SAS as a database via MS Access. ODBC and OLE DB are standard approaches from MS Access, if you have a running SAS instance (like SAS/Share or a SAS Workspace) that you can connect to.