SQL and data library files 1

[Xzibit7]

I have a List of Part Numbers that is in the SAS data library. I have a list in an odbc database and I am trying to match the two up. My problem is that I do not know the format for expressing my data library file in SQL. Here is my code.

PROC SQL;

CONNECT TO ORACLE AS DSDCON (USER=gsv0367 ORAPW=gsv0367 PATH=dss);

CREATE TABLE WORK.DATA AS

SELECT *

FROM CONNECTION TO DSDCON
(

SELECT

BB.NIIN,
BB.Profit_Center

FROM V_PORTAL_ZDOR_ITM BB
Where

BB.NIIN = NIINList.NIIN
);
quit;
RUN;


NIINList is imported into the data library. Any help would be appreciated. Thanks

 

[ChrisW75]

This is tricky. You're trying to join a SAS dataset to a SQL database yes?
Everything in the brackets in your code above is sent to the box the SQL table is on, and it doesn't know what a SAS dataset it. You need to use SAS/Access really to do this sort of thing. This can often be very slow as in these cases, SAS brings down the entire SQL table, THEN does the join. From memory (my SAS library seems to be missing the examples I built at my last job unfortunately) you need to do something like this:-

libname  Oralib oracle  user="wr" pw="*******" path="PDWH" preserve_col_names=yes schema=DWH;

proc sql;
  create table WORK.DATA as
  select A.blah1
        ,A.blah2
        ,B.blah2
  from  saslib.dset  A
     left join
        oralib.parts(dbkeys=partnumber dbnullkeys=NO) B
    on A.partno = B.partnumber
   ;
quit;

The dbkeys option isn't well documented, but speeds up this sort of join enormously, especially if the SAS dataset is relatively small and the Oracle table relatively large. You basically tell SAS what the key is that you are joining on, I believe that the key should be indexed to work properly. I've found that this speeds things up to a level you wouldn't beleive. An 8 hour query was reduced to under 8 minutes by adding this option in.
Let me know how you go.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[Xzibit7]

Thanks Chris very helpful!