11-21-2014 09:49 AM
my question i need a code to go from current month to previous year end of the month and
aand create macro and use that macro in oracle passthru query instead of hard code on date
11-21-2014 10:05 AM
This will yield you a date which is a year before today, in yymmdd10 format, i.e. YYYY-MM-DD, which if memory serves me, is the date OC uses. Otherwise just change the format.
11-21-2014 10:43 AM
Did you use DT as sas macro in the sql for Orcale? An how?
Better to ask clear questions with as much possible relevant information. Do not give irrelevant it will disturb and confuse.
Your questions are:
- how to deduce the current / simulation date to a defined selection date?
(answer in the dt macro variable)
- how to give a sas macro value as date to a oracle sql?
11-21-2014 11:18 AM
i have used %let x= '31dec2013'd;
callsymput ( "st_end", " ' " ||put(x, date9.)||" '");
now I used &st_end instead of 31dec2013 but I don't want to use 31dec 2013 in starting I want to go back to 31dec2013 using intnx and put it in oracle query
11-21-2014 12:05 PM
In your Oracle Query (which you haven't posted here so i can't really say) you would need to have something like:
So your macro variable you set to your the date you want. Code for this I provide in previous post. To then use that in you Oracle Query you need to convert it to numeric, as the macro variable is TEXT. See this article which describes further: http://www.pharmasug.org/proceedings/2014/AD/PharmaSUG-2014-AD19.pdf
11-21-2014 12:58 PM
HHi I have an oracle query like this
proc SQL ;
connect to oracle as mydat (user = password = path =);
Create table xy as
select * from connection to mydat
from val. abc
where ins_dt = '31-DEC-2012'
disconnect from mydat;
11-21-2014 01:03 PM
So above is my query instead of '31-DeC-2012' can I write a macro which goes to that date from today and simply include ins_dt = &stdt instead of
ins_dt = '31-DEC-2012' . (Hard coding )
and I I don't want to include 31-dec-2012 in %let and use it I want to go to that date from today
11-21-2014 01:16 PM
Just use tat &st_end in oracle coding no quotes. The macro pre-processor will change the source before it handed over, It should be exactly as your hand coded version. The only difference the value being put by that generated value.