Date:         Fri, 24 May 1996 10:58:24 PDT
Reply-To:     TWB2%Rates%FAR@GO50.COMP.PGE.COM
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         TWB2%Rates%FAR@GO50.COMP.PGE.COM
Subject:      Re: SAS linked to Visual Foxpro, MS-Access, MS-SQL Server Databa
Comments: To: bercikj@MUSC.EDU

John, There are at least two different ways to use foreign databases in SAS.
Recently, I have been using PROC ACCESS to define views of DB2 tables.  I am
building one view (used in SAS as one dataset) for each table.  Since the view
is a SAS structure, it uses eight character names.  I can allow PROC ACCESS to
default the names, or I can specify them.  This approach allows me to use the
data with very little understanding of DB2.  I suspect, though I have not
tried, I can define a view which uses DB2 to join two tables and translates
the result into SAS.  One reason I have not gone this route is that it creates
temporary tables on the DB2 system, and I do not want to do that (bad enough I
read "their" data, if I started writing to their server the lights would go
out).  Regarding data types, there may be some precision issues for large
numbers in small variables and I am not aware of a way to support OLE objects
or binary large objects (blobs) in SAS datasets.  I found our DB2 people were
lax in their database design and defined a missing value for certain fields
(rather than hanging it in a separate segment which would not exist if the
value was missing).  This is a problem because DB2 does not support the
concept of missing values so I have to code to exclude the dates of 12/31/9999
(which show up in MMDDYY8. as 12/31/99 and look fine).
 
There is also a feature called passthru which seems to let you pass commands
directly to the foreign database.  This may be the machine-efficient
(?programmer-inefficient?) way to process a query which needs to join several
tables with few matches, since it avoids bringing the non-matched data over to
SAS to reject it.
 
I mainly use the SAS/Access Reference and the C&E (P-221).  I have the oddly
named SAS/ACCESS Software Changes and Enhancements SQL Procedure Pass-Through
Facility (no P number), but I have not used passthru yet.
 
Tim Berryhill - Contract Programmer and General Wizard
TWB2@PGE.COM or http://www.lookup.com/Homepages/92062/home.html
Frequently at Pacific Gas & Electric Co., San Francisco
The correlation coefficient between their views and
my postings is slightly less than 0
----------------------[Reply - Original Message]----------------------
 
Sent by:John Bercik <bercikj@MUSC.EDU>
 Please respond to "bercikj@musc.edu" to ensure delivery.  Thanks.....
 
I'm trying to get sas to link to my databases and am wondering what I can
expect when this is working properly.
 
Will SAS honor the long field names?
 
Will SAS be able to handle all data types?  Text, date......
 
Will SAS be able to look at many tables and join them or will I need to use
the
Make Table Query in the database program to form a single table SAS can work
with?
 
What books are a must have to figure this out for Visual Foxpro, MS-Access,
and MS-SQL Server Databases?
 
 
 
--
Thank you very much for your time and SAS input,
John Bercik
Medical University of South Carolina
Systems Manager of the General Clinical Research Center
Charleston, SC  803-792-6729
Web address     http://www.gcrc.musc.edu
email:          bercikj@musc.edu
 
=====================================================================