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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.