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