Date: Wed, 1 Sep 2004 23:07:05 -0400
Reply-To: "Miller, Jeremy T." <zyp9@CDC.GOV>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Miller, Jeremy T." <zyp9@CDC.GOV>
Subject: Re: deleting records from MS Access table
Content-Type: text/plain; charset="us-ascii"
Thanks for all of the .net tips. I should've included more info. I get
data from states in some messy xml, but, some cleaning must be done and
vars added--which, SAS does more easily than ADO. I only have
Access2002.
The way I read in the xml data is through SAS map files (originally
using ATLAS). eg.
filename a 'Q:\Projects\EIP_HepSurv\CO\XML\CO_Case.xml';
filename MAP_A 'Q:\Projects\EIP_HepSurv\CO\XML\Maps\Cases_A.map';
libname a xml xmlmap=MAP_A access=READONLY;
What I was going to do, was output a few temp files and use DBMS Copy to
replace the files, but I'm getting a huge problem with the dates. The
dates arrive as MMDDYYYY, which SAS has no problems reading. But, the
transfer to Access changes them to DATETIME19.--and that makes all the
dates 1960--I guess ATLAS shrugged. Even if I format the date in the
temp SAS file to DATETIME19., SAS even puts it at 1960. I figured that
SAS would have the date correctly and just leave the time at 12 am.
I've tried a lot of different things here, and nothing seems to work.
Funny though, the table in ACCESS I want to append to was originally
created with SAS using EXPORT. I think ACCESS must store all of its
dates as a datetime.
I did get some update files from SAS tech support for their Atlas
program a while back, but I don't think that's the issue because I can
read the data fine.
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Alan Churchill
Sent: Wednesday, September 01, 2004 10:42 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: deleting records from MS Access table
And ADO.NET really excels at doing XML.
No mention is made of the version of Access. Access 2003 directly
imports XML data. You can certainly access this functionality via .NET
and thereby code a simple import program and, if SAS is needed in the
middle, call the program and use SAS's standard in and out to pump the
data. Plus Access 2003 ‘should' come with VBA that enables the
import.
Another consideration, if you have flexibility, is to look at the SQL
Express editions that just came out and us the SQL Server functionality.
SQL Server directly supports XML as well.
Finally, SAS also supports XML so you could keep your data in SAS if
you so choose.
Alan
Savian
"Bridging SAS and Microsoft technologies"
nntp://news.qwest.net/comp.soft-sys.sas/<200409020010.i820ABhw001223@lis
tserv.cc.uga.edu>
On Wed, 1 Sep 2004 17:44:52 -0400, Miller, Jeremy T. <zyp9@CDC.GOV>
wrote:
>I have some xml data that I stick into MS Access tables. I can't
export >the to Access mdb because replacing the table will destroy the
>relationships in the Access table. > >A couple of times a month, I
get new xml data. This data is cumulative, >so, I can replace the whole
table. > >So, the solution is to PROC APPEND the xml data into the
Access tables, >but only the shell of the table--no observations else I
would have >dupes.
...
Hi, Jeremy,
I am quite sure if removing all the records in the table is neccessary.
You said that the input data is cumulative. I would rather first find
out what are the new records in the input dataset(think of set
operators like EXCEPT in sql), and append only the new ones. This way,
you don't have to delete anything. You don't need to drop and create
tables either, which may be very cumbersome depending on the kind
constraints in effect.
I am also wondering why you are going through sas to do this, instead
of going directly from xml to access? ADO supports xml very well. Well,
not that I am objecting your using sas (which is wonderful!), but I
thought you would rather want to avoid data conversion.
HTH.
Cheers,
Chang
[comp.soft-sys.sas]
