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;
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;
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.
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.