topic Re: SQL function in SAS Health and Life Sciences

SQL function

lloraine — Mon, 26 Nov 2007 20:10:05 GMT

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?

Re: SQL function

Olivier — Tue, 27 Nov 2007 13:11:40 GMT

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

Regards.
Olivier

Re: SQL function

JasonDiVirgilio — Tue, 29 Jan 2008 19:05:19 GMT

...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.

Re: SQL function

Doc_Duke — Tue, 29 Jan 2008 21:50:35 GMT

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