@jayeshmenon
The TRIMMED keyword will populate the macro variable without leading blanks.
If you just want to determine the longest string in variable QUOTE_DISPLAY from either of the two source datasets then I believe your SQL might not necessarily return the correct result.
In case there is a value from both tables the coalesce function will pick the first value - and should it so happen that the longest string is in the 2nd variable then you'll miss it.
A simple data _null_ step like below will not only perform better but also return the correct result.
libname memwork "%sysfunc(pathname(work))";
data memwork.EDW_ITCPOLRTEATT memwork.QDS_ITCPOLRTEATT;
length QUOTE_DISPLAY_ID $10;
PROTO_POLICY_ID=1;
QUOTE_DISPLAY_ID='333';
output memwork.EDW_ITCPOLRTEATT;
QUOTE_DISPLAY_ID='55555';
output memwork.QDS_ITCPOLRTEATT;
stop;
run;
PROC SQL NOPRINT;
SELECT MAX(LENGTH(COALESCEC(t1.QUOTE_DISPLAY_ID,t2.QUOTE_DISPLAY_ID)))
INTO :QteDispLen TRIMMED
FROM MEMWORK.EDW_ITCPOLRTEATT t1
FULL JOIN
MEMWORK.QDS_ITCPOLRTEATT t2
ON (t1.PROTO_POLICY_ID = t2.PROTO_POLICY_ID)
;
QUIT;
%put &=QteDispLen;
data _null_;
set
MEMWORK.EDW_ITCPOLRTEATT(keep=QUOTE_DISPLAY_ID)
MEMWORK.QDS_ITCPOLRTEATT(keep=QUOTE_DISPLAY_ID)
end=last;
retain _max_len;
_max_len=max(_max_len,lengthn(QUOTE_DISPLAY_ID));
if last then call symputx('QteDispLen',_max_len);
run;
%put &=QteDispLen;
And if you're a SQL guy then I believe the SQL should look like below:
proc sql noprint;
select max(lengthn(QUOTE_DISPLAY_ID))
into :QteDispLen TRIMMED
from
(
select QUOTE_DISPLAY_ID
from MEMWORK.EDW_ITCPOLRTEATT
union corr all
select QUOTE_DISPLAY_ID
from MEMWORK.QDS_ITCPOLRTEATT
)
;
quit;
%put &=QteDispLen;
... View more
