topic Re: Need help understanding proc sql "Connect to " in SAS Programming

Need help understanding proc sql "Connect to "

Shawnty — Thu, 05 Apr 2018 11:45:25 GMT

hello,

I have a question about running connect to option in PROC SQL. Currently im able to do calculations as such:
proc sql feedback;
connect to odbc as DB (dsn="[name]");
create table work.Want as select * from connection to DB
(
select t1.*,
divide(t2.A,t2.B) as ABC
from DB.[Scheme].[Have_on_server] AS t1
left join work.[have_in_work] AS t2 on t1.A=t2.A
);
disconnect from DB ;
quit;
Where i take a dataset from server, left join a dataset from my work and put the dataset in my work.

But if i instead take a dataset from work and left join a dataset on server, this does not work. Does anyone have a clue as of why it isnt working?

proc sql feedback;
connect to odbc as DB (dsn="[name]");
create table work.Want as select * from connection to DB
(
select t1.*,
divide(t2.A,t2.B) as ABC
from work.[Have_in_work] as t1
left join DB.[Scheme].[Have_on_server] AS t2 on
(t1.A=t2.A)
);
disconnect from DB ;
quit;

Re: Need help understanding proc sql "Connect to "

Shawnty — Thu, 05 Apr 2018 11:47:20 GMT

I may add that the reason id like to do this, is that the process takes a very long time on PC. When i do this with a dataset on server and leftjoin work i save about 97% time.

If you cant solve this problem, but know another way to calculate my merge on server this is also greatly appreciated.

Re: Need help understanding proc sql "Connect to "

LinusH — Thu, 05 Apr 2018 12:18:16 GMT

Hi Shawnty,

there a tons of similar questions on the forum, do a search and hopefully you will get some input.

Basics: CONNECT TO works soly in the realms of the external data source, everything sent there have no clues of your local environment.

So the trick is merge these worlds together somehow.

Techniques include uploading your local data set to the server as a volatile table (similar to a SAS work table), or by performing the join using a libname reference to your server table, then could use the DBKEY= option, and if local data is small enough SAS may transform the join key to a WHERE clause sent to the RDBMS table.