topic Re: How do I use Today function in Proc Sql in New SAS User

How do I use Today function in Proc Sql

VarunD — Tue, 18 Dec 2018 17:06:40 GMT

Hello,

I am getting the following errror

ERROR 22-322: Syntax error, expecting one of the following: MISSING, NOT, NULL, ^, ~.

ERROR 76-322: Syntax error, statement will be ignored.

When I run this code :

proc sql;
create table MEME as
select subscriber_id
, subscriber_name
,(datepart(Subscriber_Termination_DT)) as Term_DT

from sashelp.susbcriber_table
where TERM_DT is ge today();
quit;

* Subscriber_Termination_DT is in Datetime22.3 format

Re: How do I use Today function in Proc Sql

novinosrin — Tue, 18 Dec 2018 17:18:13 GMT

is ge

should

proc sql;
create table MEME as 
select subscriber_id
, subscriber_name
,datepart(Subscriber_Termination_DT) as Term_DT

from sashelp.susbcriber_table
where calculated TERM_DT  ge today();
quit;

Re: How do I use Today function in Proc Sql

VDD — Tue, 18 Dec 2018 17:12:00 GMT

%let today = today();

data want;
TERM_DT = &today.;
run;

Re: How do I use Today function in Proc Sql

Jagadishkatam — Tue, 18 Dec 2018 17:13:55 GMT

we do not require the 'is' before 'ge'

proc sql;
create table MEME as 
select subscriber_id
, subscriber_name
,(datepart(Subscriber_Termination_DT)) as Term_DT
from sashelp.susbcriber_table
where TERM_DT ge today();
quit;

Re: How do I use Today function in Proc Sql

novinosrin — Tue, 18 Dec 2018 17:20:48 GMT

A better illustration:

/*Sample dataset HAVE */
data have;
do datetm=intnx('dtday',datetime(),-2) to intnx('dtday',datetime(),+2) by 86400;
output;
end;
format datetm datetime20.;
run;

/*Output dataset MEME */
proc sql;
create table MEME as 
select datepart(datetm) as Term_DT format=date9.
from have
where calculated TERM_DT  ge today();
quit;

Re: How do I use Today function in Proc Sql

VarunD — Tue, 18 Dec 2018 17:52:04 GMT

Thanks for your quick response! I appreciate it.

Re: How do I use Today function in Proc Sql

VarunD — Tue, 18 Dec 2018 17:52:17 GMT

Thank you!

Re: How do I use Today function in Proc Sql

VarunD — Tue, 18 Dec 2018 17:52:31 GMT

Thanks a lot !

Re: How do I use Today function in Proc Sql

novinosrin — Tue, 18 Dec 2018 18:03:29 GMT

Also, I beg your pardon as I forgot to add a very important note in terms of efficiency. It's always better to filter or subset using where processing before SQL processor reads every record i.e in other words ideally the SQL processor should only select records that satisfy a condition meaning pick only the subset and not process the entire set, and then filter based on a computed column.

Often the better way is

where  datepart(datetm)  ge today();

rather than

where  calculated TERM_DT  ge today();

Full illustration below:

/*Sample dataset HAVE */
data have;
do datetm=intnx('dtday',datetime(),-2) to intnx('dtday',datetime(),+2) by 86400;
output;
end;
format datetm datetime20.;
run;
/*Output dataset MEME */
proc sql;
create table MEME1 as 
select datepart(datetm) as Term_DT format=date9.
from have
where  datepart(datetm)  ge today();
quit;

HTH & Regards!