BookmarkSubscribeRSS Feed
chennupriya
Quartz | Level 8

sas macro date format for oracle

11 REPLIES 11
chennupriya
Quartz | Level 8

HI ,

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


image.jpg
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

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.

data _null_;

     call symput('DT',put(intnx('year',today(),-1),yymmdd10.));

run;

chennupriya
Quartz | Level 8

HI,

but it the Oracle prepare error : ORA-00932

inconsistent data types expected Date got number

jakarman
Barite | Level 11

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? 

---->-- ja karman --<-----
chennupriya
Quartz | Level 8

HI ,

i have used %let x= '31dec2013'd;

data_null_;

callsymput ( "st_end", " ' " ||put(x, date9.)||" '");

Run;

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

In your Oracle Query (which you haven't posted here so i can't really say) you would need to have something like:

TO_DATE("&ST_END.", 'DDMONYYYY')

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

chennupriya
Quartz | Level 8

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

(

select *

from val. abc

where ins_dt = '31-DEC-2012'

);

disconnect from mydat;

chennupriya
Quartz | Level 8

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

jakarman
Barite | Level 11

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.

---->-- ja karman --<-----
chennupriya
Quartz | Level 8

Ok I got it Thanks  for help Smiley Happy

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1948 views
  • 0 likes
  • 3 in conversation