How to handle missing macro variables with Coalescec function

[David_Billa]

In the following code, I want the macro variable _SOURCE_CD to resolve it as missing. Also I don't wish explicitly create that macro variable with missing before I call this macro variable. Could you please help me to resolve this? 

%let _LEGAL_CD=RMN;

data l;
lel=compress(COALESCEC("&_SOURCE_CD.","&_LEGAL_CD."));
call symput('lel',lel);
run;

Log:

26         %let _LEGAL_CD=RMN;
27         
28         
29         data l;
30         lel=compress(COALESCEC("&_SOURCE_CD.","&_LEGAL_CD."));
WARNING: Apparent symbolic reference _SOURCE_CD not resolved.
SYMBOLGEN:  Macro variable _LEGAL_CD resolves to RMN
31         call symput('lel',lel);
32         run;

NOTE: The data set WORK.L has 1 observations and 1 variables.

Output:

lel

&_SOURCE_CD.

Desired Output:

lel

RMN

[andreas_lds]

Interesting, looks like a bug - or strange feature. I can't see a reason why coalescec returns the name of the unresolvable variable, instead of the value of _Legal_CD. If parameter positions in coalescec are switched, LEL is set  as expected. I can't think of a beautiful way to solve this.

[Kurt_Bremser]

---

@andreas_lds wrote:

Interesting, looks like a bug - or strange feature. I can't see a reason why coalescec returns the name of the unresolvable variable, instead of the value of _Legal_CD. If parameter positions in coalescec are switched, LEL is set  as expected. I can't think of a beautiful way to solve this.

---

That's because coalescec() does not see a variable, it sees text. The macro processor tries to resolve the macro variable, does not find it, and so throws a WARNING and lets the text stay as it is. And so the data step compiler compiles a non-empty string literal for the coalescec() function, and since at execution time the function now gets a non-missing value as its first argument, it returns that. This is very clearly not a bug, just a mistaken use of macro variables.

[andreas_lds]

@Kurt_Bremser thanks for correcting the gibberish i wrote ...

[Kurt_Bremser]

In order to use a macro variable, it MUST be defined.

So you need to set it to empty at least:

%let _SOURCE_CD=;

[David_Billa]

Just expanding my question. If the macro variables which was in the Coalescec function not exist then I want to put the value as missing so that I can get the desired value in my Output.

Is this possible?

[Kurt_Bremser]

You need to make use of the SYMEXIST and SYMGET functions, and use a temporary variable to get a completely clean log:

%let _LEGAL_CD=RMN;


data l;
if symexist("_SOURCE_CD")
then _source_cd = symget("_SOURCE_CD");
else _source_cd = '';
lel=compress(COALESCEC(_source_cd,"&_LEGAL_CD."));
call symput('lel',lel);
drop _:;
run;

[David_Billa]

I ran as below to handle both the variables but it is not working as excepted. Am I missing something?

[Kurt_Bremser]

You accidentally commented your second %let.

[David_Billa]

No, I purposely commended the second let Statement to see how the code behaves. If you the code, you can see the changes which I made from your Version.

[Kurt_Bremser]

As @gamotte mentioned, you omitted using the data step variables in the coalescec function.

[David_Billa]

Could you please help me correct it?

[Kurt_Bremser · Accepted solution]

data l;
if symexist("_SOURCE_SYS_CD")
then _SOURCE_SYS_CD = symget("_SOURCE_SYS_CD");
else _SOURCE_SYS_CD = '';
if symexist("_LEGAL_ENTITY_CD")
then _LEGAL_ENTITY_CD = symget("_LEGAL_ENTITY_CD");
else _LEGAL_ENTITY_CD = '';
le=compress(COALESCEC(_SOURCE_SYS_CD,_LEGAL_ENTITY_CD));
call symput('le',le);
drop _:;
run;

[Quentin]

Edited per @gamotte's reminder of macro statement %SYMEXIST, thanks!

I wouldn't use DATA step for this, since you're not processing data.  You can just do your COASLESCE with an %IF statement, e.g.:

%if %symexist(_SOURCE_SYS_CD) %then %do ;
  %let lel=&_Source_SYS_CD ;
%end ;
%else %do ;
  %let lel=&_LEGAL_CD ;
%end ;

[gamotte]

Note that sysfunc is not necessary here as there exists a macro %symexist equivalent.