Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Regular Contributor
Posts: 240
Accepted Solution

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

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


Accepted Solutions
Solution
‎11-08-2012 12:39 PM
Valued Guide
Posts: 634

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

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

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

View solution in original post


All Replies
Valued Guide
Posts: 634

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

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;

Occasional Contributor
Posts: 11

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

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

Regular Contributor
Posts: 240

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

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

Super User
Posts: 5,426

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

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

Data never sleeps
Solution
‎11-08-2012 12:39 PM
Valued Guide
Posts: 634

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

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

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

Regular Contributor
Posts: 240

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

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;

Occasional Contributor
Posts: 11

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

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.

Regular Contributor
Posts: 240

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

Thanks i missed that

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 18504 views
  • 8 likes
  • 4 in conversation