Date:         Tue, 26 Nov 2002 18:09:39 -0500
Reply-To:     Mike Rhoads <RHOADSM1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mike Rhoads <RHOADSM1@WESTAT.COM>
Subject:      Re: SAS Schema generation
Content-Type: text/plain; charset="iso-8859-1"

A couple of additional notes ...

With more recent versions of SAS, I suspect that you could easily get your
table "shells" into your RDBMS, assuming you have table creation privileges
and the appropriate SAS/ACCESS product, via the following:

options obs=0;
libname sasdata ... ;
libname mydbms  ... ;
proc copy  in=sasdata  out=mydbms;
run;

Be a little careful -- I haven't tested this, since our neighborhood DBAs
are careful NOT to grant me table creation privileges   ;-)

Also, if you have SAS/ACCESS for Sybase or Oracle, they do provide direct
support for the DBMS bulk loaders -- check the SAS documentation for
details.

The fact that SAS really has only two data types (numeric and character)
makes it difficult to optimize loading SAS data into an RDBMS, since there
is no obvious way of knowing whether SAS numeric fields are best represented
as integer (to say nothing of what size), floating point, or what.  (Or, for
that matter, whether CHAR fields are better stored as fixed or varying
length.)  You could, of course, do some programming to look at the
characteristics of the data to generate the necessary information.  Be
particularly careful of date and datetime fields.

Mike Rhoads
Westat
RhoadsM1@Westat.com

-----Original Message-----
From: Sigurd Hermansen
Sent: Tuesday, November 26, 2002 12:56 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: SAS Schema generation


I've tried to capture SAS schema in an older version of Erwin, but without
success. The CA rep tells me that the new and improved Erwin, Allfusion,
will not reverse engineer a schema from a SAS library. A kludge method uses
the SAS SQL Create table x like y statement to create shells for each SAS
table in a library. One can then import the shells into a RDBMS that
generates ER diagrams and embellish the data model with data integrity
constraints. I have not tried importing SAS datasets with contraints already
defined.

One you have a schema defined in a RDBMS, look for a bulk loader to populate
the tables. The RDBMS provide bulk loader utilities for a reason. It usually
take forever to insert rows into tables.

Sig

-----Original Message-----
From: Krishna Kumar [mailto:kriskumar@MAILANDNEWS.COM]
Sent: Monday, November 25, 2002 11:26 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: SAS Schema generation


Friends I have a very simple problem, due to the way RAD development was
done at some point, some of the earlier developers have stopped maintaining
the schema in documents or in other forms.

Could you suggest an easy way of pulling sas schema out , if its impossible
how do i bulk export an entire sas library with tables to sybase or other
rdbms.

many thanks
Krishna