Date:         Fri, 12 Mar 2004 15:19:09 -0500
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: odbc problem
Comments: To: William Kossack <kossackw@NJC.ORG>
Content-Type: text/plain

Wm:
The SAS-L archives have a thread on this topic. As I recall, the way
SAS/ACCESS handles spaces in table names depends on the version of SAS. If
you read the table names from dictionary.tables, the results will show the
form of the table names as SAS sees them. In a pass-thru query, you will as
a rule have to put the table names in the form that they appear in MS
Access.

You might also try
- putting a macrovariable name in double quotes so it will resolve as a
string containing embedded blanks;
- invoking the ODBC engine in a LIBNAME and making the table name a 'name
literal' ('<string>'n).
Sig


-----Original Message-----
From: William Kossack [mailto:kossackw@NJC.ORG]
Sent: Friday, March 12, 2004 12:04 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: odbc problem


I'm trying to do an odbc connection to an access database (a database that
someone else created)

To my surprise the table names have spaces in them so when I go to run the
following cold it blows up

data _null_;
 set table_list nobs=nobs end=end;
 retain cnt 0;
 cnt + 1;
 put table cnt;
  call symput('bdatanam' || left(cnt), trim(table));
  call symput('bmax',left(cnt));
run;

%macro b_extract; /******************************************************/
/* use a loop to retrieve each table from the database*/
/******************************************************/
%do x=1 %to &bmax;

%let datanam = %trim(&&bdatanam&x);
 Proc SQL;
  connect to odbc(dsn=gsk);
  Create table &dataname as select * from connection to odbc
  (select * from &dataname);
  disconnect from odbc;
%end;
run;
   %mend b_extract;
   %b_extract;


Proc sql seems to have problems with the spaces in the table name in the
line
  (select * from &dataname);