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;
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.
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)"?
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.
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.
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.