from SAS to Access.

[sk1]

I have to export SAS file into Access database. I am using DBMS engine to accomplish this. I have couple of quetions-
1. 1st one of the variables is char 255$ and it gives an error when I export this field. Length of 200$ works fine. But I would like to export out the full 255 $ field, is there a way to accomplish this?

2. and 2nd, I am not sure how to overwrite the existing table in Access(from SAS) before I create the new one using following code. Is there some Option I can add?


LibName dblib2 DbODBC Source="CATIDBInst"
dbtbl2="tblInstData";
run;
data dblib2.dbtbl2;
set Inst;
run;

Thanks
SK

 

[klaz2002]

SK,
If you want to export a SAS dataset to access why not use the Proc Export from sas.
Here is what works for me.

PROC EXPORT DATA= PROTO.FINAL
OUTTABLE= "final"
DBMS=ACCESS2000 REPLACE;
DATABASE="C:\temp\my_data.mdb";
RUN;

The 'replace' keyword tells sas to overwrite the existing access table. You may not have access2000 in that case just substitute 'access97' for the dbms perameter.

I hope this helps you.
klaz

 

[sk1]

Thanks Klaz2002,
I had tried using Proc Export but it didn't work.. here is the error from the log using the Code you provided..
any thoughts? ( I have Access97 SAS 8.2)
1332 PROC EXPORT DATA= PROTO.FINAL
1333 OUTTABLE= "tblYNData"
1334 DBMS=ACCESS97 REPLACE;
7 The SAS System

ERROR: DBMS type ACCESS97 not valid for export.
ERROR: DBMS type ACCESS97 not valid for export.
ERROR: DBMS type ACCESS97 not valid for export.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE EXPORT used:
real time 0.00 seconds
cpu time 0.00 seconds

1335 DATABASE="D:\Files\Mellon\CATIProcess\SpecifyDatabase\MelnCATIYesNo_092003.mdb";
1336 RUN;

 

[klaz2002]

SK,
OOps. Try 'ACCESS' not access97. My bad.
Klaz

 

[sk1]

Sorry but no luck, same error with "Access" as well
SK

 

[klaz2002]

Could it be a license issue? Run proc setinit (proc setinit;run. Check to see if your site (office) has the access/odbc (also known as acc/odbc) license.

If you are not licensed by SAS, you may be able to convert your data in a two step process. First you output the sas dataset in csv format. Then open access and load that (csv) file the linking wizard will open. Just follow the instructions.
klaz

 

[sk1]

that's it.. we don't have acc/odbc license but there is
SAS/ACC-OLE DB
thanks for all your help.. CSV could work but we wanted to automate the process from SAS so that the SAS programmers can run the SAS job and create an Access table from SAS. The
DBMS engine works well except for those two constraints I mentioned above.
-thanks for your time
SK.

 

[teralearner]

Have you tried DDE?
Here is a link on DDE and Access:

http://ftp.sas.com/techsup/download/sample/datastep/msaccess.html

The example is reading from Access, but it can be easily reversed.

 

[klaz2002]

SK,
Just to give you a little more info. The proc access that you say worked will only do 200 chars max. The reason for this is that proc is a version 6 procedure. SAS told me that they are not dropping the proc but have mainstreamed most of the capabilities in their libname statement. This is the odbc SAS engine that you say your place has no license for. The example that works for me using the new libname statement is as follows:

LIBNAME TEMP ODBC DSN="MY_DSN_FILE";

This gives me a way to read an outside datasource, but the write part is tricky. You can create an outside datasource (if it doesn't exist), but not change the outside data using the standard SAS routines. There is a way that I found if you generate SAS SQL code using SET statements that worked. This all needs the SAS ODBC engine to work.
Hope this helps.
Klaz