Date: Fri, 18 Oct 2002 21:13:41 -0400
Reply-To: "Karl K." <karlstudboy@HOTMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Karl K." <karlstudboy@HOTMAIL.COM>
Subject: Re: Thanks - RE: I need to automate this please
May I pose a follow-up question on this topic?
We pretty much have to live with data from our clients in whatever (Windows)
databases they give us, incl. MS-Access, Visual Fox, Excel and, of course,
SQL Server. Because these databases get updated frequently, we typically do
more or less what Jeff decided to do below, i.e., use ODBC to build views
into the live databases. Recently, we got a SQL Server database with about
a gazillion tables in it, and I took a different approach.
Using the ODBC applet in Windows Control Panel, I set up a User DSN to
connect to the database. This, of course, needed to be done on every
workstation that needed access to that SQL Server database. Then, in SAS,
all my programmers need to do was issue a simple libname statement like:
libname <whatever> odbc dsn="<User_DSN_Name>" user="xxxx" pw="xxxx";
(Most of them put this in their autoexec.sas for the duration of the
project, so it was always available.)
All the tables are accessible to any sas command in standard
<libname>.<tablename> format. You can click in the SAS Explorer pane and
see the library. You can double-click on it and see the names of every
table in the database. You can right click and get properties, etc. You
can still create views from them, if you want, even using the pass-thru
facility.
So what's my question? It's this: Is this somehow more efficient or more
elegant than Jeff and Nancy's big macro and multiple resulting views? Both
solutions require an appropriate libname statement to get at the data, so,
my alternative solution just requires an ODBC User DSN (granted, on every
relevant workstation). There's no need to create one view per table in the
database. Does anybody see any advantage to one method over the other? My
gut tells me that the User DSN route is more elegant, but I confess I can't
think of any compelling reasons.
Any thoughts? TIA.
Karl
"Jeff Morison" <jmt_mtf@YAHOO.COM> wrote in message
news:20021018232730.44258.qmail@web40807.mail.yahoo.com...
> Thanks Nancy and to all who responded,
> this is what I did.
>
> (ODBC::SQLTables,,,"TABLE") is the clue to get all
> the tables in SQL Server.
>
>
> proc sql;
> connect to sqlservr(server=phildcut83
> database=racerep user=xxxxxx password=xxxxxx);
> create view list as
> select table_name
> from connection to sqlservr
> (ODBC::SQLTables,,,"TABLE")
> order by table_name;
> disconnect from sqlservr;
> quit;
>
> /*********** MACROTIZING THE LIST OF TABLE NAMES
> **************************/
>
> /**** One table had more than 32 charcters in its name
> *****/
>
> data list_new;
> set list;
> if table_name='t_adm_Letter_Variable_Assignments' then
>
> table_name='t_a_Ltr_Var_Assgns';
> run;
>
>
> proc sql noprint;
>
> select count(distinct table_name) into :n_tbls
> from list_new;
>
> select distinct(table_name) into
> :tbl1-:tbl%TRIM(%LEFT(&n_tbls))
> from list_new;
>
> quit;
> run;
>
> /************** CREATING THE SAS VIEWS OF THE SQL
> SERVER DATABASE TABLES ****************/
> %MACRO CREAT;
> proc sql;
> connect to sqlservr(server=phildcut83
> database=racerep user=xxxxxx password=xxxxxx);
>
> %do i=1 %to &n_tbls;
> create view raceview.&&tbl&i as
> select *
> from connection to sqlservr
> (select *
> from &&tbl&i);
> %end;
>
> disconnect from sqlservr;
> quit;
> %MEND CREAT;
> %CREAT;
>
>
> Jeff.
>
>
>
> --- "Brucken, Nancy" <Nancy.Brucken@pfizer.com> wrote:
> > Hi Jeff,
> > Does SQL Server have an equivalent to Oracle's
> > ALL_TABLES table (not sure
> > if that's the exact name, but it's close), which
> > contains information on all
> > of the tables in the database? If so, you ought to
> > be able to extract the
> > names of all of the tables into a macro variable,
> > and loop through that list
> > to drive your view creation.
> > Hope this is at least a start,
> > Nancy
> >
> > Nancy Brucken
> > Development Informatics
> > Pfizer Global Research & Development, Ann Arbor
> > (734) 622-5767
> > E-mail address: Nancy.Brucken@pfizer.com
> >
> >
> > -----Original Message-----
> > From: Jeff Morison [mailto:jmt_mtf@YAHOO.COM]
> > Sent: Friday, October 18, 2002 1:04 PM
> > To: SAS-L@LISTSERV.UGA.EDU
> > Subject: I need to automate this please
> >
> >
> > I came up with this code to create a SAS view from
> > a
> > MS SQL Server table, but there are tons of tables in
> > that server, I don't want to manually type each
> > table
> > name and create a SAS View, is there an easy way to
> > get the list of all the tables in the SQL Server
> > database and macrotize this code to create a SAS
> > view
> > for each table.
> >
> > Your help will be highly appreciated.
> >
> > TIA,
> > Jeff
> >
> >
> > proc sql;
> > connect to sqlservr(server=phildcut83
> > database=racerep user=xxxxxx password=xxxxxx);
> > create view raceview.t_History_change_log as
> > select *
> > from connection to sqlservr
> > (select *
> > from t_History_change_log );
> > disconnect from sqlservr;
> > quit;
> >
> > __________________________________________________
> > Do you Yahoo!?
> > Faith Hill - Exclusive Performances, Videos & More
> > http://faith.yahoo.com
>
>
> __________________________________________________
> Do you Yahoo!?
> Faith Hill - Exclusive Performances, Videos & More
> http://faith.yahoo.com
| 
