Desktop productivity for business analysts and programmers

SAS EG SQL Help

Accepted Solution Solved
Reply
Contributor
Posts: 70
Accepted Solution

SAS EG SQL Help

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;


Accepted Solutions
Solution
‎08-31-2012 05:22 AM
Respected Advisor
Posts: 4,137

Re: SAS EG SQL Help

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.

View solution in original post


All Replies
Respected Advisor
Posts: 4,137

Re: SAS EG SQL Help

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

Contributor
Posts: 70

Re: SAS EG SQL Help

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.

Solution
‎08-31-2012 05:22 AM
Respected Advisor
Posts: 4,137

Re: SAS EG SQL Help

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.

Contributor
Posts: 70

Re: SAS EG SQL Help

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 257 views
  • 1 like
  • 2 in conversation