sql execution based on condition

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

sql execution based on condition

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;


Accepted Solutions
Solution
‎06-01-2017 08:27 PM
Super User
Posts: 9,682

Re: sql execution based on condition

[ Edited ]


        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;

View solution in original post


All Replies
Super User
Posts: 6,946

Re: sql execution based on condition

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
PROC Star
Posts: 172

Re: sql execution based on condition

[ Edited ]

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

Solution
‎06-01-2017 08:27 PM
Super User
Posts: 9,682

Re: sql execution based on condition

[ Edited ]


        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 topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 154 views
  • 3 likes
  • 4 in conversation