SQL substr vs. ANSI substring: put-LeftAlign works differently

[MatthiasB]

Hi,

I am currently optimizing and shortening some huge SQL code and came across a problem which I have no explanation for.

In some SQLs I use "put (..., 10.-L)" to create a new variable, I shortened it from "left (put (..., 10.))" before. All works fine, result is left-aligned. I copied the "put"-assignment to some other SQL code, and it does not work there, the resulting text is right-aligned.

I broke it down to a problem with creating substrings: using SAS's "substr" anywhere in the SQL statement the result is left-aligned, using the ANSI SQL "substring" it is suddenly right-aligned... to bad when used as a key to reference other tables. It is easy to code around this, but I would like to understand what is actually happening here.

Example code:

data dummy;
  txt = 'DuMmY-TeXt';
  do nval=-5 to 5; output; end;
run;

data ds_tab; %*-- L-align in DataStep.  ;
  set dummy;
  tval_ds = put (nval, 10.-L);
run;

proc sql; %*--  L-align in SQL w/substr function SAS.  ;
  create table sql_tab_substr as
    select *,
           put (nval, 10.-L) as tval_sql_substr,
           substr (txt, 1, 1) as txt_sql_substr
    from ds_tab;
quit;

proc sql; %*--  L-align in SQL w/substrING function ANSI SQL.  ;
  create table sql_tab_substring as
    select *,
           put (nval, 10.-L) as tval_sql_substring,
           substring(txt from 1 for 1) as txt_sql_substring
    from sql_tab_substr;
quit;

data _null_;
  set sql_tab_substring;
  if _n_ = 1 then
    put 'From DS     SAS-sub     ANSI SQL';
  put tval_ds $10. '  ' tval_sql_substr $10. '  ' tval_sql_substring $10.;
run;

Looking at the table or the log output I see that the put-L did not work when using "substring":

From DS     SAS-sub     ANSI SQL
-5          -5                  -5
-4          -4                  -4
-3          -3                  -3
-2          -2                  -2
-1          -1                  -1
0           0                    0
1           1                    1
2           2                    2
3           3                    3
4           4                    4
5           5                    5

Can anyone explain why a put-LEFT does not work anymore when using ANSI SQL "substring" in an SQL-statement? I have no clueless...

Thanks!

Matthias

[Kurt_Bremser]

Bring this to the attention of SAS Technical Support. I think you have stumbled across a bug.

[Tom]

Why are you using SUBSTRING() in PROC SQL instead of PROC FEDSQL?

[MatthiasB]

I am looking into existing SQL code written by testers and developers who are new to SAS development and not necessarily know some of the features. There is a mix of ANSI and SAS functions used here in PROC SQL, and when I step by step changed code blocks I made the format use -L to shorten left(put(...)) in all occurences. That broke the join on a key and I was nosy enough to investigate why one SQL did work and the other didn‘t. so mid of cleanup.

[ChrisNZ]

This sure looks like a big fat bug.

This function is supposed to work for proc sql and proc fedsql, and the wonky behaviour is the same with proc fedsql.

Please keep us updated on what SAS TS says.

[MatthiasB]

Tech Support Germany has passed this on to Cary (mail conversation from Sep 28th), they reckon it will be 9.4 M7. Since I have a workaround, I had them close my ticket: didn't need to track this, especially since I have no control when which version is updated on servers here (z/OS and Linux, both on different TS-Levels). So, please watch out for M7 and "what's new" says.

[Kurt_Bremser]

What troubles me here especially: the presence or non-presence of one function in the code has an influence on the workings of another function. This points to side-effects and a breach of compartmentalization. The old coder in me shivers with horror.