Date:         Wed, 16 Jan 2002 14:16:24 -0500
Reply-To:     "Tift, Brian" <bet5@CDC.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Tift, Brian" <bet5@CDC.GOV>
Subject:      Re: Data Management in MSACCESS from SAS
Comments: To: "Alte@Uni-Greifswald.de" <Alte@Uni-Greifswald.de>
Content-Type: text/plain

Dietrich,

Here is what I use.  We have SAS/ACCESS to ODBC licensed which may make a
difference.
I have never tried using PROC DBLOAD.  The SAS_Test.dsn is file data source
name that points
to a specific Access data base, works well when you need to access a db from
multiple computers just save
the .dsn file to a network location.

libname test odbc noprompt="filedsn=Q:\ODBC_DSNs\SAS_Test.dsn";

/* A - creating a table in an existing *.mdb */
data test.NewTable;
 do i=1 to 10;
  a=i*2;
  b=a*i;
 output;
 end;
run;

/*  OR  */

proc SQL;
 create table Test.NewTable2
  (var1 char(5), var2 char(3), var3 num) ;
quit;

/* B - adding a row to a table */

proc sql;
  insert Into Test.Newtable2
   (var1,var2,var3)
    Values ("a","b",3);
  insert into Test.NewTable2
   (var1,var2,var3)
    Values ("c","d",8);
quit;

/* C - changing values of an exiting row */

proc sql;
  update test.newtable2
        set var3=9
         where var1="c";
quit;



-----Original Message-----
From: Dietrich Alte [mailto:alte@MAIL.UNI-GREIFSWALD.DE]
Sent: Wednesday, January 16, 2002 11:31 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Data Management in MSACCESS from SAS


You all,

after trying to create, add, modify, insert data into existing
MS-ACCESS 2000 tables:

Could some one please supply example code for

A - creating a table in an existing *.mdb
B - adding a row to a table
C - changing values of an exiting row

I am bit confused from the different possibilites of PROC SQL, DBLOAD
and LIBNAME statement.

TIA

(SAS 8.2, WINpro)

--
-----------------------------------------------------------------
 Dietrich Alte (Statistician, Dipl.-Stat.)
 University of Greifswald - Medical Faculty
 Institute of Epidemiology and Social Medicine
 Walther-Rathenau-Str. 48, D-17487 Greifswald, Germany
 Phone      +49 (0) 3834 - 86 77 13, fax +49 (0) 3834 - 86 66 84
 Email      alte@mail.uni-greifswald.de
 Institute  http://www.medizin.uni-greifswald.de/epidem/
 Study      http://www.medizin.uni-greifswald.de/epidem/ship.htm
-----------------------------------------------------------------