How do I use Today function in Proc Sql

[VarunD]

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

[novinosrin · Accepted solution]

is ge

should

ge

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;

[novinosrin]

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;

[VarunD]

Thanks for your quick response! I appreciate it.

[novinosrin]

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!

[VDD]

%let today = today();

data want;
 TERM_DT = &today.;
run;

[VarunD]

Thank you!

[Jagadishkatam]

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;

[VarunD]

Thanks a lot !