There are some types of functions that work in different ways in SQL from the data step (they are called "aggregate" functions, like mean, sum, etc.). For those, look at the SQL reference manual.
Your question is sufficiently naive that you may want to do some background reading. There are (at least) two Books-By-Users that are available on the SAS/Publishing web site that address SAS and SQL. Prairie's might be a good place to start, followed by Lafler's book.
For your specific example, the SQL might look something like
CREATE TABLE xyz AS
Eff_Frm_Dt GE TODAY()
as the result-equivalent of
IF Eff_Frm_Dt GE TODAY();