SAS SQL Length function

[Citrine10]

Hi there

I am trying to perform the below SQL code in a proc sql in SAS

SQL code:
select
reference
into
#Wi
from #all
where Reference in (select left(Reference ,len(Reference )-1)+ max(right(Reference ,1))
from stgw
group by left(Reference ,len(Reference )-1))
group by Reference , UCN1
order by ucn1

I tried:

proc sql;

create table Wi as

select
reference
from all
where Reference in (select left(Reference ,len(Reference )-1)+ max(right(Reference ,1))
from stgw
group by left(Reference ,substr(Reference, lenght(Reference)-1,0)))
group by Reference , UCN1
order by ucn1

Error:

ERROR: Function LEN could not be located.
ERROR: Expression using subtraction (-) requires numeric types.

How can I perform the length function in SAS PROC SQL?

[Tom]

Please explain what the original query is actually doing. 

Your syntax appears be mixing strings and numbers. 

left(Reference ,len(Reference )-1) + max(right(Reference ,1))

The LEFT() function will move all of the leading spaces in a string to trailing spaces.

The RIGHT() function will move all of the trailing spaces in a string to leading spaces. 

Both only take one argument. 

The MAX() SQL aggregate function will take the string that collates last for the group. 

You then try to use addition with the resulting strings.

So what meaning does that strange code have in the original SQL dialect you were using?

[LinusH]

It's called LENGTH() in SAS.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lefunctionsref/n0f6jve4kdxnh1n1m7c82fhosgih.h...