SQL function

Reply
Contributor
Posts: 58

SQL function

I am so new to SAS that I am not sure how to even ask a question but here goes - I want to say the following in a Where statement:

Eff_Frm_Dt GE to TODAY. How do I use functions in a Where statement?
Super Contributor
Posts: 260

Re: SQL function

Juste add a pair of parenthesis to TODAY, so as it is known as a function.
[pre]
Eff_Frm_Dt >= TODAY()
[/pre]

Regards.
Olivier
Contributor
Posts: 45

Re: SQL function

...or if your eff_frm_dt returns a datetime value you could do:

Eff_Frm_Dt >= datetime()

or:

eff_frm_dt >= dhms(date(),00,00,00)

This is assuming that you are not connecting to a database. If you are, you'll have to format the output of the SAS date function to match whatever format the database returns a date value.
Trusted Advisor
Posts: 2,114

Re: SQL function

Linda,

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

PROC SQL;
CREATE TABLE xyz AS
SELECT
*
FROM mydata.abc
WHERE
Eff_Frm_Dt GE TODAY()
;
QUIT; RUN;

as the result-equivalent of

DATA xyz;
SET mydata.abc;
IF Eff_Frm_Dt GE TODAY();
RUN;

Doc Muhlbaier
Duke
Ask a Question
Discussion stats
  • 3 replies
  • 369 views
  • 0 likes
  • 4 in conversation