sas date

LuckyLucks
LuckyLucks used Ask the Experts™
on
How can I get yesterday's date in the following format into a macro variable to be used in the
rest of the code.
'12Oct2009:0:0:0'
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Cory VandenbergSenior Risk Manager

Commented:
Do you want to put the date into a macro variable as a datetime string or do you have a datetime and just want to store the date part as a string in a variable?

Not totally clear what you are after here.

A real simple example of how to get yesterday's date and add a midnight timestamp to it.

If you were looking for something else, please be more clear what you are after.

WC
%macro test();
%let dat8 = %sysfunc(Date());
%put Today is &dat8;
%let yest = %sysfunc(cats(%sysfunc(intnx(day,%sysfunc(Date()), -1), date9.),:0:0:0));
%put &yest;
%mend;
%test();

Open in new window

Senior Risk Manager
Commented:
Here are a couple more lines converting the datetime to just a date.

WC
%macro test();
%let dat8 = %sysfunc(Date());
%put Today is &dat8;
%let yest = %sysfunc(cats(%sysfunc(intnx(day,%sysfunc(Date()), -1), date9.),:0:0:0));
%put &yest;
%let yestDate = %sysfunc(putn(%sysfunc(datepart("&yest"dt)),date9.));
%put &yestDate;
%mend;
%test();

Open in new window

Author

Commented:
I have a followup on this : If I were to use this in my PROC SQL query where clause, I get some syntax erros

    PROC SQL;
187         CREATE TABLE WORK.Query1
              AS SELECT A.A_ID FORMAT=11.,
188              A.CLOSE_TS FORMAT=DATETIME25.6,
190              A.A_ID FORMAT=6.,
191              A.A_NM FORMAT=$80.,
192              A.A_DSC FORMAT=$256.
193         FROM A.T AS A
194         WHERE A.CLOSE_TS ='&yest'dt;
ERROR: Invalid date/time/datetime constant '&yest'dt.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Cory VandenbergSenior Risk Manager

Commented:
&yest is storing the string value of the datetime value.  You would have to convert this into a SAS datetime value in order to use it in the WHERE clause.

I believe you should be using something like

%let yestDT = %sysfunc(inputn(&yest,datetime16.));
%put &yestDT;

Then you could use &yestDT to compare, since it is a proper SAS datetime constant.

WC

Commented:
You used

   WHERE A.CLOSE_TS ='&yest'dt;

SAS doesn't expand macro variables inside single quotes.  You need to use double quotes.

So something like this would work:

   WHERE A.CLOSE_TS ="&yest"dt;

assuming that &YEST has the correct string value.  "06Feb2010:0:0:0"dt is a datetime constant in SAS; you don't need to convert it to a number using the INPUTN function in order to use it in an expression.
Cory VandenbergSenior Risk Manager

Commented:
Good to know...thank kd

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial