topic SQL Pass thru in SAS Programming

SQL Pass thru

SASPhile — Fri, 30 Nov 2012 19:24:16 GMT

proc sql;

connect to sybase (user=xxx pass=xxx server=xx connection=shared);

create table crdmis._demog_curr as
select * from connection to sybase
(
select a.*
from _demog_curr a
);
disconnect from sybase;

quit;

     proc sql;
       create table crdmis._demo_curr_v1 as
       select a.acct_no,b.cm11
from outdata_cntry_20 a left outer join crdmis.crdmis_demog_curr b
on substr(a.acct_no,1,11)=put(b.cm11,?32.)
;
quit;

How can this be done in step?The sybase table _demog_curr is so huge, it is being written in the sas library.Is there a way where both these steps be combined into one and see final table crdmis._demo_curr_v1?

Re: SQL Pass thru

Haikuo — Fri, 30 Nov 2012 20:24:52 GMT

I have dealt with several types of database; unfortunately Sybase is not one of them. However, I know that Sybase largely complies with ANSI SQL just like other database. So the absolute correctness in term of syntax will only be achieved by looking up Sybase SQL doc: http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookView

Looks like you are troubled by the slow outer join done by SAS. The approach I would suggest requires you to have “write-permission” somewhere on your Sybase server, so that you can finish your join inside Sybase using its optimized engine. Of course, code is not tested.

/*to build a workplace on your Sybase server*/

libname syb sybase user=xx password=xx server=xx;

/*to copy your SAS table back onto Sybase server*/

data syb.a;

set outdata_cntry_20;

run;

/*to let Sysbase do the join and transfer the results back to SAS*/

proc sql;

connect to sybase (user=xxx pass=xxx server=xx connection=shared);

create table crdmis._demo_curr_v1 as

select * from connection to sybase

( select * from a

left join

_demog_curr b

on substring(a.acct_no,1,11)=convert(char(32),b.cm11)

);

disconnect from sybase;

quit;

Haikuo

Re: SQL Pass thru

Reeza — Fri, 30 Nov 2012 20:34:57 GMT

I'll go the macro variable route, but there is a limit on the macro variable size. There is a way around that though if you get stuck.

You might need to fiddle with the macro variable a bit to get it in the exact method that SYBASE will need.

*not sure of the formatting required, but you can fix that;

proc sql;

select put(acct_no, char32.) as acct_no

into :varlist

separated by ', '

from outdata_cntry_20;

quit;

proc sql;

connect to sybase (user=xxx pass=xxx server=xx connection=shared);

create table crdmis._demog_curr as
select * from connection to sybase
(
select a.*
from _demog_curr a

where b.cm11 in (&varlist);
);
disconnect from sybase;

quit;

EDIT: This is link to a workaround if your macro variable is too long. No idea if it works within pass through

http://support.sas.com/kb/46/109.html

Re: SQL Pass thru

TomKari — Sat, 01 Dec 2012 15:32:30 GMT

Hi, SASPhile

I haven't done much with the Sybase engine, but I'm pretty sure both of the above suggestions will work with it.

Here are a couple more:

1. In your first query, you seem to be doing a select *, but in your second one you're only asking for cm11 from your table. If you can change your first select to "select a.cm11", you may reduce your data volume enough to make the process acceptable.

2. As a different way to tackle Reeza's suggestion, check out the following note. It shows how SAS can set up a long "where ... in(...)" clause, as a substitute for the join. I've never tried this myself, but it looks reasonable.

http://support.sas.com/documentation/cdl/en/acreldb/65247/HTML/default/viewer.htm#n0tcetvx1zpnayn1r8actrkjrd5o.htm