topic Re: How to use date/time field with a minus 1 day in SAS Procedures

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

BETO — Thu, 08 Nov 2012 00:14:37 GMT

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

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

ArtC — Thu, 08 Nov 2012 00:44:15 GMT

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;

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

Geraldo — Thu, 08 Nov 2012 02:23:56 GMT

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

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

BETO — Thu, 08 Nov 2012 07:07:37 GMT

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

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

LinusH — Thu, 08 Nov 2012 07:49:32 GMT

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).

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

ArtC — Thu, 08 Nov 2012 17:39:38 GMT

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

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

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

BETO — Thu, 08 Nov 2012 20:30:57 GMT

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;

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

Geraldo — Fri, 09 Nov 2012 01:22:42 GMT

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

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

BETO — Fri, 09 Nov 2012 02:23:06 GMT

Thanks i missed that