topic Re: sas macro date format in SAS Programming

sas macro date format

chennupriya — Fri, 21 Nov 2014 04:53:51 GMT

sas macro date format for oracle

Re: sas macro date format

jakarman — Fri, 21 Nov 2014 06:51:29 GMT

SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Fifth Edition

Re: sas macro date format

chennupriya — Fri, 21 Nov 2014 14:49:53 GMT

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

Re: sas macro date format

RW9 — Fri, 21 Nov 2014 15:05:08 GMT

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;

Re: sas macro date format

chennupriya — Fri, 21 Nov 2014 15:18:36 GMT

HI,

but it the Oracle prepare error : ORA-00932

inconsistent data types expected Date got number

Re: sas macro date format

jakarman — Fri, 21 Nov 2014 15:43:05 GMT

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?

Re: sas macro date format

chennupriya — Fri, 21 Nov 2014 16:18:32 GMT

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

Re: sas macro date format

RW9 — Fri, 21 Nov 2014 17:05:46 GMT

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

Re: sas macro date format

chennupriya — Fri, 21 Nov 2014 17:58:47 GMT

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;

Re: sas macro date format

chennupriya — Fri, 21 Nov 2014 18:03:09 GMT

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

Re: sas macro date format

jakarman — Fri, 21 Nov 2014 18:16:29 GMT

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.

Re: sas macro date format

chennupriya — Fri, 21 Nov 2014 21:36:16 GMT

Ok I got it Thanks for help