topic Re: Joining a SAS table with a Sybase pull in SAS Programming

Joining a SAS table with a Sybase pull

Albr0166 — Fri, 02 Jul 2021 15:25:30 GMT

I'm using code inherited from someone else. The tables being used are being connected to using the sybase connection directly in the proc sql statement (see code below ). I've done some performance testing and this is faster than using a libname statement.

However, I have some tables that I'd like to pull in that are in a SAS library but since they aren't in the sybase database, that's not working. Two are basically just used for indicators so I was able to use a macro variable for that. However, one table has over 100K observations and I need to pull in a field from that table.

So my question is basically, is there a way to join a table using the sybase connection with a SAS table in one step? Or do I just need to use two steps?

Thanks!

PROC SQL;
CONNECT TO sybase(server=&server.
                  database=&databse.
                  user=&user. 
                  password=&password.);

Create table Table as
Select *
From Connection to Sybase(

Select 
.
.
.
%put &sqlxmsg;
Disconnect from Sybase;
quit;

Re: Joining a SAS table with a Sybase pull

ChrisNZ — Sat, 03 Jul 2021 02:26:33 GMT

Yes you can.

The code in blue below is SAS code and the code in red is Sybase code.

You can add any SAS code at all. For example:

libname SYB sybase server=&server database=&databse user=&user pwd=&pwd;

proc sql;

connect using SYB;

create table TABLE as
select *
from connection to SYB (

select ...

) a

left join

SASLIB.SASTABLE b

on a.ID=b.ID

;

%put &sqlxmsg;

quit;

Re: Joining a SAS table with a Sybase pull

Albr0166 — Tue, 06 Jul 2021 13:43:52 GMT

@ChrisNZ That's really helpful. This is (probably) a stupid question, but how would I pull in fields from table B?

Re: Joining a SAS table with a Sybase pull

SASKiwi — Tue, 06 Jul 2021 20:12:47 GMT

Add them to your SELECT statement.

Re: Joining a SAS table with a Sybase pull

ChrisNZ — Tue, 06 Jul 2021 22:11:07 GMT

select * means all variables from all tables