DATA Step, Macro, Functions and more

Proc SQL

Reply
N/A
Posts: 0

Proc SQL

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.
Super Contributor
Posts: 260

Re: Proc SQL

Posted in reply to deleted_user
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
N/A
Posts: 0

Re: Proc SQL

Posted in reply to deleted_user
Thanks Oliver... That was really helpful !
Ask a Question
Discussion stats
  • 2 replies
  • 631 views
  • 0 likes
  • 2 in conversation