BookmarkSubscribeRSS Feed
Shawnty
Obsidian | Level 7

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;

2 REPLIES 2
Shawnty
Obsidian | Level 7
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.
LinusH
Tourmaline | Level 20

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.

Data never sleeps

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 694 views
  • 0 likes
  • 2 in conversation