How to use date/time field with a minus 1 day

[BETO]

Hi Ihave to date formatted  diffrently currently when we use the script we just change date to ysterday date. I want to automated and have tried Now(), Date()  today()

-1 but get an error saying function name is not found.

Enclose is  snapost of the 2 types of date/time I'm working with

1)  where Date = {ts'2012-11-04 00:00:00'}

2)proc sql;

CREATE TABLE Part2 AS

(SELECT

AtmCodeNumber,

CurrInitAmt AS Initialized,

TranDate

FROM sql.Hist

and TranDate = '04Nov2012:00:00:00'dt

and CurrInitAmt NE 0

GROUP BY AtmCodeNumber,TranDate

);

run;

Thank you again for your assitance

[ArtC]

We are going to need a bit more information here.

I do not see the DATE() or TODAY() function in your code. Those functions exist and should be available so there is something else going on.  I am unfamiliar with the NOW() function.

How are you attempting to use the date string     where Date = {ts'2012-11-04 00:00:00'} ?

This is not a SAS date constant is it a DB2 date? Are you attempting a SQL pass-thru?

What error are you getting in your SQL step?

To answer your primary question; SAS dates are stored as numeric displacement values (number of days since 1jan1960).  So yesterday's date is a subtraction.

yesterday = today() - 1;

[Geraldo]

Beto.

Verify exemple....

data teste;

data_dia = '04nov2012 00:00:00'dt;

run;

proc sql;

select datepart(data_dia) format=ddmmyy10., datepart(data_dia -1) format=ddmmyy10. from teste;quit;

For data step exist intnx function... verify

[BETO]

Here is my code  with errors message.. The 1st table works as the data step. The issue is when  to select today date minus 1 or 3

options symbolgen;

%LET START = TODAY(SYSDATE,'DD-MON-RR') -3;

proc sql;

CREATE TABLE Part1 AS select * from connection to ODBC

(SELECT

Date,

CodeNumber,

Sum(Amount) AS ReplenAmount

FROM tbVendors

where DATE > {ts'2012-11-04 00:00:00'}

GROUP BY Date, cOdeNumber,Amount

ORDER BY CodeNumber);

Run;

data Part1;

set Part1;

date=datepart(ReplenDate);

format date MMDDYY8.;

run;

proc sql;

CREATE TABLE First AS

(SELECT

Date,

CodeNumber,

Sum(Amount) AS ReplenAmount

FROM Part1

Where Date >= &START

);

Run;

ERROR: Function TO_DATE could not be located.

ERROR: Expression using subtraction :smileyminus: requires numeric types.

ERROR: The following columns were not found in the contributing tables: SYSDATE

[LinusH]

As Art already mentioned, your SAS syntax is wrong.

The today function takes no arguments.

Datetime constants as you did in your first post ('datetime-value'dt).

[ArtC · Accepted solution]

&start will contain yesterday's date if you use:

%let start = %eval(%sysfunc(today())-1);

[BETO]

Hi Art,

Thank you so much for your assitance  and all who responded . I was able to fig out enclose is what I use 

data _null_;

call symput('yesterday', "'"||put(today()-1, mmddyy10.)||"'");

run;

proc sql;

CREATE TABLE Part1 AS select * from connection to ODBC

(SELECT

Date,

CodeNumber,

Sum(Amount) AS ReplenAmount

FROM tbVendors

where DATE =&yesterday

GROUP BY Date, cOdeNumber,Amount

ORDER BY CodeNumber);

Run;

[Geraldo]

Hi beto,

The macro variable no compile/change  with '

change de data step with:

data _null_;

call symput('yesterday',put(today()-1, mmddyy10.));

run;

change the proc sql with:

proc sql;

CREATE TABLE Part1 AS select * from connection to ODBC

(SELECT

Date,

CodeNumber,

Sum(Amount) AS ReplenAmount

FROM tbVendors

where DATE ="&yesterday"

GROUP BY Date, cOdeNumber,Amount

ORDER BY CodeNumber);

  quit;

The date is complicated used in the macro variable and using obdc connect is not SAS date variable, and macro variable has the format database odbc connect for success full insert.

• Re: How to use date/time field  with a minus 1 day

[BETO]

Thanks i missed that