BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BETO
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
ArtC
Rhodochrosite | Level 12

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

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

View solution in original post

8 REPLIES 8
ArtC
Rhodochrosite | Level 12

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
Fluorite | Level 6

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
Fluorite | Level 6

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
Tourmaline | Level 20

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
ArtC
Rhodochrosite | Level 12

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

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

BETO
Fluorite | Level 6

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
Fluorite | Level 6

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.

BETO
Fluorite | Level 6

Thanks i missed that

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 73449 views
  • 9 likes
  • 4 in conversation