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

[Wouter]

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

[sbb]

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

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


Scott Barry
SBBWorks, Inc.

[sbb]

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

[Wouter]

> 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'?

[sbb]

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.

[Wouter]

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

[sbb]

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.

[Wouter]

> 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