topic How do I get a multi-level ODBC path to work? in SAS Programming

How do I get a multi-level ODBC path to work?

FatCaptain — Tue, 01 Nov 2011 11:40:31 GMT

I have the following libname which connects to an SQL Server via OBDC;

libname servdat odbc dsn="MyODBC" uid=dbid pwd=pass ;

The tables on this server all have a period in their name, for example a.cus_data

The SQL is falling over as I belive that it sees this as a multi-level connection string.

proc sql ;

select *

from servdat.a.cus_data

;

quit ;

Is there a way I can get this to work?

Thanks in advance for any help.

Paul.

How do I get a multi-level ODBC path to work?

Doc_Duke — Tue, 01 Nov 2011 15:38:53 GMT

One way to do it is to convert the program to pass-thru SQL instead of using the libname approach.

How do I get a multi-level ODBC path to work?

FatCaptain — Tue, 01 Nov 2011 16:10:26 GMT

Thanks Doc. This does the job nicely.

proc sql;

connect to odbc(user=myodbc password=mypass dsn="MYODBC");

create table outdat as

select * from connection to odbc

(select * from a.cus_data);

quit;

How do I get a multi-level ODBC path to work?

Ksharp — Wed, 02 Nov 2011 08:23:28 GMT

There is an option preserve_tab_names= . and Did you try 'a.cus_data'n this literal table name?

Ksharp

data x;
input months $12.;
datalines;
111111111111
000000000000
100000000000
110000000000
111000000000
000000000001
000000000011
000000000111
000001000000
000001100000
000011100000
110001000000
111100000111
101011100100
;
run;
data want(drop=mon con);
set x;
mon=translate(compbl(translate(months,' ','1')),'_',' ');
con=countc(mon,'_');
if con=1 then do;
                  select(findc(mon,'_'));
                   when(1) flag='B';
                   when(length(mon)) flag='F';
                   otherwise flag='M';
                  end;
                end;
    else flag='O';
run;

How do I get a multi-level ODBC path to work?

darrylovia — Fri, 04 Nov 2011 18:33:25 GMT

did you try the owner= option

for SQL Server it is usually dbo