Date:         Wed, 6 Oct 2004 23:04:53 -0400
Reply-To:     "Richard A. DeVenezia" <radevenz@IX.NETCOM.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Richard A. DeVenezia" <radevenz@IX.NETCOM.COM>
Subject:      Re: Proc SQL & Macro Var

Barz, Ken wrote:
> I've gotten a bit rusty on my SAS lately. I'm trying to create a
> program that will dynamically generate a data dictionary based on the
> schema view in SQL Server. What I would like to do is use the macro
> variable Table_List (which is a comma delimited list of all the data
> tables) to loop through data steps and create a data set
> corresponding to each table. Is this possible or is there a better
> way? Here's my code so far:
>
> proc sql;
>   connect to odbc as mydb (dsn=sql);
>
>   create table Data_Dictionary as (
>     select * from connection to mydb
>       (select Table_Name,
>        Column_Name,
>        Data_Type,
>        Character_Maximum_Length,
>        Numeric_Precision,
>        Numeric_Scale,
>        Ordinal_Position,
>        Is_Nullable
>         from information_schema.columns
>         where SUBSTRING ( Table_Name , 1 , 3 ) = 'tbl'
>           and SUBSTRING(TABLE_NAME,len(table_name)-4,LEN(TABLE_NAME))
>           <> 'AUDIT' and Table_Name not in
>         ('tbl_Contact','tbl_Counts','tbl_Technicians') order by
>   table_name, ordinal_position) );
>   disconnect from mydb;
>
> quit;
>
> proc sql noprint;
>   select distinct Table_Name
>    into :Table_List separated by ','
>   from Data_Dictionary;
> quit;
>
> %put Table_List = &Table_List;  * n=28 table names;
>
> /* Loop over Table_List to create data sets here */

Ken gets a list of tables in SQL Server whose names do not meet some
criteria.
The criteria may not work as expected due to the variations in character
case in the string literals used to identify name parts of concern.
Also substring()  could be a problem if len(table_name) < 4.

If Data dictionary is wanted on the SAS side, then creating it is no biggy.
However, the select :into could occur in the first query.  Indeed, if only
the tables that are 'to be copied' are of concern then the first query could
be reduced greatly.  Also, the criteria have no concern for column_name...
only table_name.  There might be a simpler SQL server table to utilize for
getting table_names... perhaps information_schema.tables (instead of
.columns)

There are plenty of step boundaries being utilized, so a call execute might
be simpler.

Don't need to do the into.  Proceed right into a data _null_ that generates
the SQL statements that will copy the desired SQL server tables over to SAS
work library.

data _null_;

  retain lib 'WORK';

  set data_dictionary end=end;
  by table_name;

  if _n_ = 1 then call execute 'proc sql; connect to odbc as mydb
(dsn=sql);'  ;

 if not last.table_name then delete;


 call execute
('create table ' || trim(lib)||'.'||trim(table_name) || ' as select * from
connection to mybd '
|| ' (select * from ' || trim(table_name) || ');'
);

if end then call execute ('disconnect from mydb; quit;');

run;


--
Richard A. DeVenezia
http://www.devenezia.com/