DATA Step, Macro, Functions and more

sas macro date format

Reply
Frequent Contributor
Posts: 120

sas macro date format

sas macro date format for oracle

Valued Guide
Posts: 3,208

Re: sas macro date format

Frequent Contributor
Posts: 120

Re: sas macro date format

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
Super User
Super User
Posts: 7,415

Re: sas macro date format

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;

Frequent Contributor
Posts: 120

Re: sas macro date format

HI,

but it the Oracle prepare error : ORA-00932

inconsistent data types expected Date got number

Valued Guide
Posts: 3,208

Re: sas macro date format

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 --<-----
Frequent Contributor
Posts: 120

Re: sas macro date format

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

Super User
Super User
Posts: 7,415

Re: sas macro date format

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

Frequent Contributor
Posts: 120

Re: sas macro date format

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;

Frequent Contributor
Posts: 120

Re: sas macro date format

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

Valued Guide
Posts: 3,208

Re: sas macro date format

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 --<-----
Frequent Contributor
Posts: 120

Re: sas macro date format

Ok I got it Thanks  for help Smiley Happy

Ask a Question
Discussion stats
  • 11 replies
  • 489 views
  • 0 likes
  • 3 in conversation