Help using Base SAS procedures

LEFT function in SAS-SQL creates a warning, but it's plain SQL

Reply
Contributor
Posts: 62

LEFT function in SAS-SQL creates a warning, but it's plain SQL

Hi,

I've created a (SAS) SQL query, but this generate a warning ("Function LEFT requires at most 1 argument(s). The extra one(s) will be ignored."). In Management Studio the query (this part) works great, but not in SAS. Can anyone see the problem?

The 'faulty' part:

WHEN LEFT(MAX(Addr.ZipCode),3) IN ('101', '102') OR MAX(Addr.ProvinceName) IN ('Berlin', 'Brandenburg') THEN 'E'
WHEN MAX(Addr.ProvinceName) IN ('Baden', 'Hamburg') THEN 'W' END AS RM_EW

Thanks for your answer!!

Message was edited by: Wouter Message was edited by: Wouter
Super Contributor
Super Contributor
Posts: 3,174

Re: LEFT function in SAS-SQL creates a warning, but it's plain SQL

Was this test? Count your parenthesis characters in the expression below:

LEFT(MAX(Addr.ZipCode)),3)


Scott Barry
SBBWorks, Inc.
Super Contributor
Super Contributor
Posts: 3,174

Re: LEFT function in SAS-SQL creates a warning, but it's plain SQL

Also, the SAS LEFT function is different than other programming language LEFT function behavior and specification. You will need to consider another approach such as using SUBSTR.

Scott Barry
SBBWorks, Inc.

SAS Language Reference: Dictionary - LEFT function:
http://support.sas.com/documentation/cdl/en/lrdict/62618/HTML/default/a000212224.htm
Contributor
Posts: 62

Re: LEFT function in SAS-SQL creates a warning, but it's plain SQL

> Also, the SAS LEFT function is different than other
> programming language LEFT function behavior and
> specification. You will need to consider another
> approach such as using SUBSTR.
>
> Scott Barry
> SBBWorks, Inc.
>
> SAS Language Reference: Dictionary - LEFT function:
> http://support.sas.com/documentation/cdl/en/lrdict/626
> 18/HTML/default/a000212224.htm

But this is the LEFT function for DATA steps. I've wrote a SQL query (proc sql); the LEFT function should be the SQL 'version'?
Super Contributor
Super Contributor
Posts: 3,174

Re: LEFT function in SAS-SQL creates a warning, but it's plain SQL

Who's SQL language should be your first question? Like the diagnostic states, only one argument is expected for the SAS LEFT function -- regardless of whether it is PROC SQL or DATA / PROC step invocation.

Scott Barry
SBBWorks, Inc.
Contributor
Posts: 62

Re: LEFT function in SAS-SQL creates a warning, but it's plain SQL

> Who's SQL language should be your first question?
> Like the diagnostic states, only one argument is
> expected for the SAS LEFT function -- regardless of
> whether it is PROC SQL or DATA / PROC step
> invocation.
>
> Scott Barry
> SBBWorks, Inc.

Thank you for your answer. So if I understand correctly, SQL functions called within SAS SQL like "LEFT" can have another meaning/impact?
Super Contributor
Super Contributor
Posts: 3,174

Re: LEFT function in SAS-SQL creates a warning, but it's plain SQL

Yes - It's somewhat (I'm stretching but it's beer:30 - sorry!) akin to the difference between "pot holder" and "roach clip" -- the first (that being LEFT) was already taken so they had to come up with another name -- the SAS function is SUBSTR.

Scott Barry
SBBWorks, Inc.
Contributor
Posts: 62

Re: LEFT function in SAS-SQL creates a warning, but it's plain SQL

> Was this test? Count your parenthesis characters in
> the expression below:
>
> LEFT(MAX(Addr.ZipCode)),3)
>
>
> Scott Barry
> SBBWorks, Inc.

Hmmm... had tried some things with parenthesis, and forgot to delete one. But the warning didn't change after entering extra parenthesis. The correct version which created the warning was: LEFT(MAX(Addr.ZipCode),3) --> I've changed it now also in the 1st post. Thanks!

Message was edited by: Wouter Message was edited by: Wouter
Ask a Question
Discussion stats
  • 7 replies
  • 5227 views
  • 0 likes
  • 2 in conversation