DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

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

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.


Accepted Solutions
Solution
‎11-01-2011 11:38 AM
Trusted Advisor
Posts: 2,115

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

Posted in reply to FatCaptain

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

View solution in original post


All Replies
Solution
‎11-01-2011 11:38 AM
Trusted Advisor
Posts: 2,115

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

Posted in reply to FatCaptain

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

Contributor
Posts: 29

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

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;

Super User
Posts: 10,020

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

Posted in reply to FatCaptain

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;
Frequent Contributor
Posts: 139

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

did you try the owner= option

for SQL Server it is usually dbo

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 183 views
  • 0 likes
  • 4 in conversation