SQL Pass thru

[SASPhile]

      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?

[Haikuo]

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

[Reeza]

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

[TomKari]

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#n0tcetvx1zpnayn1r8...