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

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

Wouter — Tue, 06 Oct 2009 14:50:57 GMT

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

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

sbb — Tue, 06 Oct 2009 14:55:29 GMT

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

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

Scott Barry
SBBWorks, Inc.

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

sbb — Tue, 06 Oct 2009 14:58:35 GMT

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

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

Wouter — Tue, 06 Oct 2009 15:05:37 GMT

> 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

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

Wouter — Tue, 06 Oct 2009 15:06:45 GMT

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

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

sbb — Tue, 06 Oct 2009 15:23:00 GMT

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.

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

Wouter — Wed, 07 Oct 2009 21:37:26 GMT

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

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

sbb — Wed, 07 Oct 2009 21:44:43 GMT

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.