Sometimes I want a code block to differ slightly depending on the value of a parameter input to a macro. This is easily done using a %if statement with the %str function to add a block of code if a certain condition is met. Some examples are shown below: the first one shows adding a join in the FROM statement if the MARKET parameter = ‘MF’, while the second example adds more fields to the SELECT statement depending on the value of the POST_ELIG_MONTHS parameter. Don’t forget to add a semi-colon after the %if statement. You can use this in many different ways to add flexibility to your code.
Example 1:
%macro cohort_rx(cohort=,market=,index_yr=,post_elig_months=);
proc sql;
create table drv_com_&cohort as
select distinct
a.nhi_individual_id,
a.index_dt
from proj.index_com_&cohort a
join proj.elig_&cohort._com b on a.nhi_individual_id=b.nhi_individual_id
%if %unquote(%str(%’&market%’))=’MF’ %then %str(
join proj.dx_com_&cohort._&market. c on a.nhi_individual_id=c.nhi_individual_id
)
;
where b.months_elig_pre>=3
and b.months_elig_post>=&post_elig_months
and a.index_yr=&index_yr
and a.nhi_individual_id not in(select nhi_individual_id from proj.prior_rx_com_&cohort)
;
quit;
%mend;
Example 2:
proc sql;
create table proj.persistency_&cohort._sum as
select
%unquote(%str(%’&cohort%’)) as cohort
,’Persistent’ as metric
,sum(case when p1=1 then 1 else 0 end) as p1 label=’Month 1′
,sum(case when p2=1 then 1 else 0 end) as p2 label=’Month 2′
,sum(case when p3=1 then 1 else 0 end) as p3 label=’Month 3′
,sum(case when p4=1 then 1 else 0 end) as p4 label=’Month 4′
,sum(case when p5=1 then 1 else 0 end) as p5 label=’Month 5′
,sum(case when p6=1 then 1 else 0 end) as p6 label=’Month 6′
%if &post_elig_months=12 or &post_elig_months=24 %then %str(
,sum(case when p7=1 then 1 else 0 end) as p7 label=’Month 7′
,sum(case when p8=1 then 1 else 0 end) as p8 label=’Month 8′
,sum(case when p9=1 then 1 else 0 end) as p9 label=’Month 9′
,sum(case when p10=1 then 1 else 0 end) as p10 label=’Month 10′
,sum(case when p11=1 then 1 else 0 end) as p11 label=’Month 11′
,sum(case when p12=1 then 1 else 0 end) as p12 label=’Month 12′
)
;
%if &post_elig_months=24 %then %str(
,sum(case when p13=1 then 1 else 0 end) as p13 label=’Month 13′
,sum(case when p14=1 then 1 else 0 end) as p14 label=’Month 14′
,sum(case when p15=1 then 1 else 0 end) as p15 label=’Month 15′
,sum(case when p16=1 then 1 else 0 end) as p16 label=’Month 16′
,sum(case when p17=1 then 1 else 0 end) as p17 label=’Month 17′
,sum(case when p18=1 then 1 else 0 end) as p18 label=’Month 18′
,sum(case when p19=1 then 1 else 0 end) as p19 label=’Month 19′
,sum(case when p20=1 then 1 else 0 end) as p20 label=’Month 20′
,sum(case when p21=1 then 1 else 0 end) as p21 label=’Month 21′
,sum(case when p22=1 then 1 else 0 end) as p22 label=’Month 22′
,sum(case when p23=1 then 1 else 0 end) as p23 label=’Month 23′
,sum(case when p24=1 then 1 else 0 end) as p24 label=’Month 24′
)
;
from proj.persistency_&cohort
group by
1,2
;
quit;