topic Re: SAS EG SQL Help in SAS Enterprise Guide

SAS EG SQL Help

D_Z_ — Thu, 30 Aug 2012 23:12:29 GMT

I have some code that I am trying to work with. I am working with two paremeters. First one is Date, which I have and it works no issues. The second is a product code and I cannot seem to get that to generate no matter what i do or how i change the code. I put my entire code below... can someone please tell me what I am doing wrong... i am not an SQL expert by any means. I think my head is sore from beating it. TYTY Dean

data _null_;
      call symput("Start_Date",put(&START_DATE,date9.));
      call symput("End_Date",put(&END_DATE,date9.));
      call symput("Product_Code",put(&Product_Code.));
run;

%put Start Date Equal To: &START_DATE;
%put End Date Equal To: &END_DATE;
%put Product Code Equal To: &Product_Code;

Proc SQL noprint;
connect to oracle (user='xxxx' orapw='xxxxxxxx' path=x preserve_comments);
option missing = '0';
create table work.TEMP_PDTCODE as select * from connection to oracle 
  ( SELECT/*+ INDEX (AO ODR_PK) USE_NL(AO)  */
  AO.DT_CRTD,
  AO.ODR_NUMBER,
  AI.PDT_PRODUCT_CODE,
  AO.AGREED_DELIVERY_DATETIME,
  AO.ACTUAL_DELIVERY_DATETIME,
  AO.DELIVERY_CONFIRMATION_FLAG

        
      
           FROM  
           ATS_ORDERS               AO ,
           ATS_ORDER_ITEMS            AI
           
     WHERE AO.DT_CRTD >= trunc(to_date(%str(%'&Start_Date%'),'ddmonyyyy'))
     AND   AO.DT_CRTD <  trunc(to_date(%str(%'&End_Date%'),'ddmonyyyy')+1)
     and   AI.PDT_PRODUCT_CODE = (%str(%'&Product_Code%'))
     AND   AO.ODR_NUMBER = AI.ODR_ODR_NUMBER
     AND   AO.VERSION = AI.ODR_VERSION
     
     
   );

Disconnect from Oracle;
Quit;

Re: SAS EG SQL Help

Patrick — Thu, 30 Aug 2012 23:41:27 GMT

If you have written this code then I believe it's sufficient to ask you: What is the intention of your 3rd "call symput" statement? Especially of "put(&product_code)"?

Re: SAS EG SQL Help

D_Z_ — Fri, 31 Aug 2012 04:13:55 GMT

I have three criteria that i need to search by as the database is too huge to query everything. I need to search by dates and product code... i basically matched the statements i knew worked for dates and tried to modify logically to get the result. I am guessing that the 3rd call symput may not be needed or the syntax is way off.

Re: SAS EG SQL Help

Patrick — Fri, 31 Aug 2012 09:22:02 GMT

The result of call symput is a macro variable. But you're already passing a same named macro variable as value. If this macro var already contains your product code then this step is not needed at all - else it would need to look a bit different and you would have to tell us what value the macro variable has.

Re: SAS EG SQL Help

D_Z_ — Fri, 31 Aug 2012 13:17:26 GMT

Makes sense. Thank you so much... i removed the symput statement and the put statement... and just by removing those two statements.. it worked and the code worked perfectly. Best of all is you helped me learn something new.

Thanks,

Dean