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
... View more
