Hi @Community_Help ! Looking for help with the code below. I created some macros and I am trying to call the macro in either the data step or the proc sql (just need one of them), but cannot get this to work. Any guidance would be greatly appreciated! %let mth_code = 202301; %let current_year = 2023; %let prev_year = 2022; %let next_year = 2024; %MACRO Week1; if substr(put(&mth_code.,6.),5,2) = '01' then Week1 = (cats(&prev_year.,'52'))*1; else if substr(put(&mth_code.,6.),5,2) = '02' then Week1 = (cats(¤t_year.,'04'))*1; else if substr(put(&mth_code.,6.),5,2) = '03' then Week1 = (cats(¤t_year.,'08'))*1; else if substr(put(&mth_code.,6.),5,2) = '04' then Week1 = (cats(¤t_year.,'12'))*1; else if substr(put(&mth_code.,6.),5,2) = '05' then Week1 = (cats(¤t_year.,'17'))*1; else if substr(put(&mth_code.,6.),5,2) = '06' then Week1 = (cats(¤t_year.,'21'))*1; else if substr(put(&mth_code.,6.),5,2) = '07' then Week1 = (cats(¤t_year.,'25'))*1; else if substr(put(&mth_code.,6.),5,2) = '08' then Week1 = (cats(¤t_year.,'30'))*1; else if substr(put(&mth_code.,6.),5,2) = '09' then Week1 = (cats(¤t_year.,'34'))*1; else if substr(put(&mth_code.,6.),5,2) = '10' then Week1 = (cats(¤t_year.,'38'))*1; else if substr(put(&mth_code.,6.),5,2) = '11' then Week1 = (cats(¤t_year.,'43'))*1; else if substr(put(&mth_code.,6.),5,2) = '12' then Week1 = (cats(¤t_year.,'47'))*1; else Week1 = 0; %MEND; %MACRO Week2; if substr(put(&mth_code.,6.),5,2) = '01' then Week2 = (cats(¤t_year.,'06'))*1; else if substr(put(&mth_code.,6.),5,2) = '02' then Week2 = (cats(¤t_year.,'10'))*1; else if substr(put(&mth_code.,6.),5,2) = '03' then Week2 = (cats(¤t_year.,'14'))*1; else if substr(put(&mth_code.,6.),5,2) = '04' then Week2 = (cats(¤t_year.,'19'))*1; else if substr(put(&mth_code.,6.),5,2) = '05' then Week2 = (cats(¤t_year.,'23'))*1; else if substr(put(&mth_code.,6.),5,2) = '06' then Week2 = (cats(¤t_year.,'27'))*1; else if substr(put(&mth_code.,6.),5,2) = '07' then Week2 = (cats(¤t_year.,'32'))*1; else if substr(put(&mth_code.,6.),5,2) = '08' then Week2 = (cats(¤t_year.,'36'))*1; else if substr(put(&mth_code.,6.),5,2) = '09' then Week2 = (cats(¤t_year.,'40'))*1; else if substr(put(&mth_code.,6.),5,2) = '10' then Week2 = (cats(¤t_year.,'45'))*1; else if substr(put(&mth_code.,6.),5,2) = '11' then Week2 = (cats(¤t_year.,'49'))*1; else if substr(put(&mth_code.,6.),5,2) = '12' then Week2 = (cats(&next_year.,'01'))*1; else Week2 = 0; %MEND; data CMAP_sample; set cmap.Data; %Week1; %Week2; Keep CB_CUST_ID WEEK_CODE CNT_OPN_AUTO where WEEK_CODE >= Week1 and WEEK_CODE <= Week2; run; proc sql; %Week1; %Week2; CREATE TABLE CMAP_sample AS select a.CB_CUST_ID as CUST_ID, a.WEEK_CODE, a.CNT_OPN_AUTO, a.CNT_OPN_MORT, from cmap.Data a where (a.WEEK_CODE >= Week1 and a.WEEK_CODE <= Week2) ; quit;
... View more