topic Re: Today() doesnt work in where statement in SAS Programming

Today() doesnt work in where statement

pandhandj — Thu, 14 Jul 2016 12:54:39 GMT

Hi Guys, I'm new to SAS (EG 9.3) and very new here...please be gentle and use simple words!

I have inherited some code that is ran each morning, with the date manually changed each day. I want to automate this code, so i am trying to change the manual date to today(), so it doesn’t need any user input.

if i take the where...Today() statement out, i get all the dates, when i put it back in i don’t get any observations.

Thanks in advance for nay help given,

paul

proc sql;
create table work.alerts_daily as
select
intID,
datecreated,
description,
subject,
SubjectEntity,
CustomerNumber
from table.alert
where description like 'Alert1' or description like 'Alert2' or description like 'Alert3';
quit;

proc sql;

create table LN_Alert_DAY as
select distinct a. *,
b. Property

from work.alerts_daily as

a inner join work.customer_status2 as b

on a. CustomerNumber = b. ID

order by intID;

run;

proc sql;
create table work.LN_Alert_DT as
select * from LN_Alert_DAY
where Datecreated = today();
run;

Re: Today() doesnt work in where statement

Reeza — Thu, 14 Jul 2016 13:19:12 GMT

What did your manual date look like?

Most likely the variable datecreated isn't a SAS date which is what you're expecting. It could either be a character variable or a date time variable. Check the variable type and format for the DateCreated variable.

Re: Today() doesnt work in where statement

pandhandj — Thu, 14 Jul 2016 13:33:39 GMT

HI Reeza,

this is the original date:

where (DateCreated = '14jul2016:00:00:000'dt);

Re: Today() doesnt work in where statement

Reeza — Thu, 14 Jul 2016 13:37:18 GMT

That's a date time variable, not a date variable.

You can use the following instead of just today() to create a date time variable.

DateCreated = dhms(today(), 0, 0,0)

Or you could use datepart function to change dateCreated to a date variable.

Datepart(dateCreated)= today()

Re: Today() doesnt work in where statement

pandhandj — Thu, 14 Jul 2016 13:46:54 GMT

Woo! and Hoo!

thats got it going,

thanks very much Reeza.

Re: Today() doesnt work in where statement

PGStats — Thu, 14 Jul 2016 15:32:21 GMT

Please mark @Reeza's answer as accepted.