topic Re: How to handle missing macro variables with Coalescec function in SAS Programming

How to handle missing macro variables with Coalescec function

David_Billa — Thu, 05 Mar 2020 11:58:16 GMT

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

Re: How to handle missing macro variables with Coalescec function

andreas_lds — Thu, 05 Mar 2020 12:27:15 GMT

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.

Re: How to handle missing macro variables with Coalescec function

Kurt_Bremser — Thu, 05 Mar 2020 12:31:00 GMT

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

So you need to set it to empty at least:

%let _SOURCE_CD=;

Re: How to handle missing macro variables with Coalescec function

Kurt_Bremser — Thu, 05 Mar 2020 12:40:56 GMT

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

Re: How to handle missing macro variables with Coalescec function

David_Billa — Thu, 05 Mar 2020 13:14:09 GMT

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?

Re: How to handle missing macro variables with Coalescec function

Kurt_Bremser — Thu, 05 Mar 2020 13:22:37 GMT

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;

Re: How to handle missing macro variables with Coalescec function

gamotte — Thu, 05 Mar 2020 13:25:26 GMT

Hello,

You can declare the macrovariable to ensure that it exists in all contexts.

If it has already be defined, the declaration will not change its value and if

not it will be defined as a space character, which is SAS way to define missing

character variables.

How you declare a macrovariable without setting its value depends on the context.

"%global" gives the macrovariable a global scope and "%local", in a macro, limits

the sccope to the macro body. Redeclaring as global a macrovariable which has already

been defined in the local context results in an error.

1    %let _LEGAL_CD=RMN;
2
3    %global _SOURCE_CD;
4
5    data l;
6    lel=compress(COALESCEC("&_SOURCE_CD.","&_LEGAL_CD."));
7    call symput('lel',lel);
8    run;

NOTE: The data set WORK.L has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           4.09 seconds
      cpu time            0.00 seconds


9
10   %put &=lel.;
LEL=RMN

Re: How to handle missing macro variables with Coalescec function

David_Billa — Thu, 05 Mar 2020 14:06:36 GMT

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

Re: How to handle missing macro variables with Coalescec function

Kurt_Bremser — Thu, 05 Mar 2020 13:36:37 GMT

You accidentally commented your second %let.

Re: How to handle missing macro variables with Coalescec function

gamotte — Thu, 05 Mar 2020 13:38:31 GMT

Yes,

You retrieve macrovariables values in a SAS dataset columns but you still use the macrovariables

in your coalesce call instead of the dataset variables.

Re: How to handle missing macro variables with Coalescec function

andreas_lds — Thu, 05 Mar 2020 13:40:14 GMT

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

Re: How to handle missing macro variables with Coalescec function

David_Billa — Thu, 05 Mar 2020 13:42:00 GMT

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.

Re: How to handle missing macro variables with Coalescec function

Kurt_Bremser — Thu, 05 Mar 2020 13:45:02 GMT

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

Re: How to handle missing macro variables with Coalescec function

David_Billa — Thu, 05 Mar 2020 13:47:03 GMT

Could you please help me correct it?

Re: How to handle missing macro variables with Coalescec function

Kurt_Bremser — Thu, 05 Mar 2020 13:52:31 GMT

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;

Re: How to handle missing macro variables with Coalescec function

Quentin — Thu, 05 Mar 2020 14:19:46 GMT

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 ;

Re: How to handle missing macro variables with Coalescec function

gamotte — Thu, 05 Mar 2020 14:17:46 GMT

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