sql execution based on condition

[sasuser101]

i have simple code that works fine as long as :mact macro variable is not empty. But when macro variable :mact is null/blank it throws error.

How do i fix this using if then else logic so it can see if macro is null and then execute?

proc sql noprint;
   select distinct mac_id format 15. into :mact SEPARATED BY ','
   from km.dmc_f
   where king = 'No';
  quit;
 

/* Execute when macro is not null */
        proc sql;
    create table km.mac_king as
    select id.*,
    case when id.macid in (&mact.) then 'Yes'
    else '' end as Duplicated
   from km.dmc_f id;

quit;

/* Execute when macro is null */
        proc sql;
    create table km.mac_king as
    select id.*,
    '' as Duplicated
   from km.dmc_f id;

quit;

[Kurt_Bremser]

This might do it:

%macro do_this;

%let mact=;

proc sql noprint;
   select distinct mac_id format 15. into :mact SEPARATED BY ','
   from km.dmc_f
   where king = 'No';
quit;
 
%if "&mact" > " " %then %do;
/* Execute when macro is not null */
proc sql;
    create table km.mac_king as
    select id.*,
    case when id.macid in (&mact.) then 'Yes'
    else '' end as Duplicated
   from km.dmc_f id;
quit;
%end;
%else %do;
/* Execute when macro is null */
proc sql;
    create table km.mac_king as
    select id.*,
    '' as Duplicated
   from km.dmc_f id;
quit;
%end;

%mend;

%do_this

Untested, for lack of example data.

[novinosrin]

I read  a technical paper long time ago by John King @data_null__ that outlines the problem in testing missing values for macro variables. I'm afraid I don't have the link to that paper right now to share. Basically, I'd make a small adjustment to Kurt's suggestion. 

%macro do_this;

%let mact=;

proc sql noprint;
   select distinct mac_id format 15. into :mact SEPARATED BY ','
   from km.dmc_f
   where king = 'No';
quit;
 
%if %length(&mact) >0 %then %do;/*Notice here*/
/* Execute when macro is not null */
proc sql;
    create table km.mac_king as
    select id.*,
    case when id.macid in (&mact.) then 'Yes'
    else '' end as Duplicated
   from km.dmc_f id;
quit;
%end;
%else %do;
/* Execute when macro is null */
proc sql;
    create table km.mac_king as
    select id.*,
    '' as Duplicated
   from km.dmc_f id;
quit;
%end;

%mend;

%do_this

 HTH,

Naveen Srinivasan

[Ksharp · Accepted solution]

        proc sql;
    create table km.mac_king as
    select id.*,
    case when id.macid in ( 

select distinct mac_id
   from km.dmc_f
   where king = 'No'

 ) then 'Yes'
    else '' end as Duplicated
   from km.dmc_f id;
quit;