SAS EG SQL Help

[D_Z_]

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;

[Patrick]

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)"?

[D_Z_]

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.

[Patrick · Accepted solution]

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.

[D_Z_]

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