topic Re: sql execution based on condition in SAS Programming

sql execution based on condition

sasuser101 — Tue, 23 May 2017 12:09:13 GMT

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;

Re: sql execution based on condition

Kurt_Bremser — Tue, 23 May 2017 12:23:57 GMT

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.

Re: sql execution based on condition

novinosrin — Tue, 23 May 2017 12:58:59 GMT

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

Re: sql execution based on condition

Ksharp — Tue, 23 May 2017 13:45:41 GMT



        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;