Length of Computed Character Column

[jayeshmenon]

I'm trying to assign a max length to a computed column by using a macro variable that has the max length value assigned to it. So far I have been able to assign this macro variable to the computed column formula by using a proc sql statement. For example, if I have a macro variable called QteDispLen which has the value 9:

COALESCEC(t2.QuoteDisplayID,t1.QuoteDisplayID) LENGTH=&QteDispLen

However, I'm trying to do the same thing but in a query builder in SAS EG. How can I do the same as above but in the formula? So far I have been able to do so using the PUT function as below, but how do I make it dynamic using the QteDispLen macro variable?

PUT(COALESCEC(t1.QUOTE_DISPLAY_ID,t2.QUOTE_DISPLAY_ID),$9.)

[novinosrin · Accepted solution]

Not sure, what you mean by "but how do I make it dynamic using the QteDispLen macro variable?"?

Are you asking if the macro variable can be called here in your 

PUT(COALESCEC(t1.QUOTE_DISPLAY_ID,t2.QUOTE_DISPLAY_ID),$9.)

?

like

PUT(COALESCEC(t1.QUOTE_DISPLAY_ID,t2.QUOTE_DISPLAY_ID),$&QteDispLen..)

[jayeshmenon]

Yes that's what I would like to do. When I tried your suggestion, I'm getting the following error with the error from to the dollar sign:

5 SELECT (PUT(COALESCEC(t1.QUOTE_DISPLAY_ID,t2.QUOTE_DISPLAY_ID),$&QteDispLen..)) AS CALCULATION
NOTE: Line generated by the macro variable "QTEDISPLEN".
5 $ 9
_
22
200
ERROR 22-322: Syntax error, expecting one of the following: a format name, ?.

[Kurt_Bremser]

You have a leading blank in your macro variable. How do you create it?

[jayeshmenon]

This is how I'm generating the macro variable:

PROC SQL NOPRINT;
	SELECT MAX(LENGTH(COALESCEC(t1.QUOTE_DISPLAY_ID,t2.QUOTE_DISPLAY_ID)))
	INTO :QteDispLen
	FROM MEMWORK.EDW_ITCPOLRTEATT t1
	FULL JOIN MEMWORK.QDS_ITCPOLRTEATT t2 ON (t1.PROTO_POLICY_ID = t2.PROTO_POLICY_ID);
QUIT;

[Patrick]

@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;

[ballardw]

If what you have pasted is correct it appears that however you create your macro variable it has a leading space. So the resolved value is not a legal format.

You would want the value to resolve to $9.   not the apparent "$ 9." you are currently getting.

Of course trying to use somethings that a wizard such as query builder isn't expecting might result in the wizard inserting the space before the 9.

If you are not using that macro variable in multiple places then you might want to make a single macro value that is $9. instead of generating as shown. Or make a specific macro variable for this use here from the numeric such as

%let mfmt = $&QteDispLen..;

and use &mfmt instead.

It may help in future to post any code or log entries into a code box opened using the forum's {I} icon. The main message windows will reformat text, especially white space (spaces and tabs). So the diagnostic underscores that SAS supplied in the log do not appear in the correct place when pasted into a main message window.

[novinosrin]

HI @jayeshmenon  Others have pointed out that your macro variable has a leading blank and causes a tokenisation issue for the compiler. 

This is a classic situation to learn about a utility autocall macro called %cmpres

%cmpress removes your leading blank and is available throughout your SAS session. 

Example: Please notice the %cmpress autocall macro applied below

PUT(COALESCEC(t1.QUOTE_DISPLAY_ID,t2.QUOTE_DISPLAY_ID),$%cmpres(&QteDispLen..))