topic Re: Proc SQL in SAS Programming

Proc SQL

deleted_user — Tue, 08 Jul 2008 22:49:56 GMT

Hi I am a new SAS user and am having difficulty when running a query as part of the proc sql statement. The Error log reads that the following functions are NOT LOCATED.

TO_CHAR, ADD_MONTH,TRUNC,LAST_DAY,DECODE,NVL

I am an oracle PL/SQL Programmer and hence am trying to run that sql here. Do I have alternate function names that I should be using instead of the above?

Any help will be greatly appreciated. Thanks.

Re: Proc SQL

Olivier — Wed, 09 Jul 2008 08:42:01 GMT

Hi Caesar.
The SAS Sql procedure uses a mixture of SQL standard syntax and SAS functions. Those listed in your message should be replaced by almost-equivalent SAS functions (check the SAS doc about them for the syntax, use, results, and so on).

TO_CHAR --> PUT

ADD_MONTH(thisDate, nbMonths) --> INTNX("MONTH", thisDate, nbMonths) + DAY(thisDate)-1
/* the INTNX("MONTH") function returns a date on the first day of the month */

TRUNC --> FLOOR / INT / ROUND (depending on what you want to do)

LAST_DAY --> INTNX("MONTH", date, 1) - 1

DECODE --> IFC / IFN (SAS 9 functions), CASE WHEN ELSE syntax

NVL(var1, var2) --> COALESCE(var1, var2)

Regards.
Olivier

Re: Proc SQL

deleted_user — Wed, 09 Jul 2008 16:22:56 GMT

Thanks Oliver... That was really helpful !