Calling Macros

[Lkrem]

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(&current_year.,'04'))*1;
else if substr(put(&mth_code.,6.),5,2) = '03' then Week1 = (cats(&current_year.,'08'))*1;
else if substr(put(&mth_code.,6.),5,2) = '04' then Week1 = (cats(&current_year.,'12'))*1;
else if substr(put(&mth_code.,6.),5,2) = '05' then Week1 = (cats(&current_year.,'17'))*1;
else if substr(put(&mth_code.,6.),5,2) = '06' then Week1 = (cats(&current_year.,'21'))*1;
else if substr(put(&mth_code.,6.),5,2) = '07' then Week1 = (cats(&current_year.,'25'))*1;
else if substr(put(&mth_code.,6.),5,2) = '08' then Week1 = (cats(&current_year.,'30'))*1;
else if substr(put(&mth_code.,6.),5,2) = '09' then Week1 = (cats(&current_year.,'34'))*1;
else if substr(put(&mth_code.,6.),5,2) = '10' then Week1 = (cats(&current_year.,'38'))*1;
else if substr(put(&mth_code.,6.),5,2) = '11' then Week1 = (cats(&current_year.,'43'))*1;
else if substr(put(&mth_code.,6.),5,2) = '12' then Week1 = (cats(&current_year.,'47'))*1;
else Week1 = 0;
%MEND;

%MACRO Week2;
if substr(put(&mth_code.,6.),5,2) = '01' then Week2 = (cats(&current_year.,'06'))*1;
else if substr(put(&mth_code.,6.),5,2) = '02' then Week2 = (cats(&current_year.,'10'))*1;
else if substr(put(&mth_code.,6.),5,2) = '03' then Week2 = (cats(&current_year.,'14'))*1;
else if substr(put(&mth_code.,6.),5,2) = '04' then Week2 = (cats(&current_year.,'19'))*1;
else if substr(put(&mth_code.,6.),5,2) = '05' then Week2 = (cats(&current_year.,'23'))*1;
else if substr(put(&mth_code.,6.),5,2) = '06' then Week2 = (cats(&current_year.,'27'))*1;
else if substr(put(&mth_code.,6.),5,2) = '07' then Week2 = (cats(&current_year.,'32'))*1;
else if substr(put(&mth_code.,6.),5,2) = '08' then Week2 = (cats(&current_year.,'36'))*1;
else if substr(put(&mth_code.,6.),5,2) = '09' then Week2 = (cats(&current_year.,'40'))*1;
else if substr(put(&mth_code.,6.),5,2) = '10' then Week2 = (cats(&current_year.,'45'))*1;
else if substr(put(&mth_code.,6.),5,2) = '11' then Week2 = (cats(&current_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;

[Tom]

What is the QUESTION?

You cannot use SAS data step syntax in PROC SQL.  Only SQL code goes there.

What do you see in the SAS log when you run your code with the MPRINT option turned on?

What is that you want to do? Please explain.

How do the macros help you do that?  What is it that you think those macros are doing? 

[ballardw]

Since SQL does not use the IF structure at all then that is going to be right out.

You should describe what this is supposed to actually do. The code doesn't change any values based on the content of the data set so seems a bit overly complicated.

Also you may need to provide what definition of "week" you are using as that can be pretty flexible term. Such as which day of the week is the first day. I have a hard time seeing how any 202302 "week" would be number 4. What are "week1" and "week2" supposed to represent? For 202301 you apparently want 202306 and it unlikely that there are 6 normal weeks in January.

This may well be a case where use of an actual date value instead of mashed together yearmonth value would be simpler. SAS has multiple functions for working with date values, such as incrementing values and calculating intervals but not arbitrary groups of digits such as your "mth_code". Not to mention functions that will return the calendar year, month, quarter and 3 different definitions of "week" from a given date.

We do not have any of your data so can't test the code with yours.

Doesn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the "</>" to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the "</>" icon or attached as text to show exactly what you have and that we can test code against.

[Reeza]

Out of curiosity you've checked and none of the week formats/functions align with what you want to do instead of hard coding this way?

I would recommend a format instead of a macro either way, more clean.

[Kurt_Bremser]

Instead of all these (totally unnecessary) computations use dates (use the YYMMN6. format to display them) and the INTNX function. This also means that the values in your datasets have to be SAS date values.

[Reeza]

data week_formats;
input fmtname $ :20. start $ label $;
type='C';
cards;
week_one_fmt    01  52
week_one_fmt    02  04
week_one_fmt    03  08
week_one_fmt    04  12
week_one_fmt    05  17
week_one_fmt    06  21
week_one_fmt    07  25
week_one_fmt    08  30
week_one_fmt    09  34
week_one_fmt    10  38
week_one_fmt    11  43
week_one_fmt    12  47
week_two_fmt    01  06
week_two_fmt    02  10
week_two_fmt    03  14
week_two_fmt    04  19
week_two_fmt    05  23
week_two_fmt    06  27
week_two_fmt    07  32
week_two_fmt    08  36
week_two_fmt    09  40
week_two_fmt    10  45
week_two_fmt    11  49
week_two_fmt    12  01
;
run;

proc format cntlin=week_formats;
run;


%let mth_code = 202304;
%let current_year = 2023;
%let prev_year = 2022;
%let next_year = 2024;

data CMAP_sample;

if input(substr("&mth_code", 5, 2), 8.) in (1:12) then do;
 week1 = catt("&current_year", put(trim(substr("&mth_code", 5, 2)), week_one_fmt.));
 week2 = catt("&current_year", put(trim(substr("&mth_code", 5, 2)), week_two_fmt.));
end;


run;

---

@Lkrem wrote:

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(&current_year.,'04'))*1;
else if substr(put(&mth_code.,6.),5,2) = '03' then Week1 = (cats(&current_year.,'08'))*1;
else if substr(put(&mth_code.,6.),5,2) = '04' then Week1 = (cats(&current_year.,'12'))*1;
else if substr(put(&mth_code.,6.),5,2) = '05' then Week1 = (cats(&current_year.,'17'))*1;
else if substr(put(&mth_code.,6.),5,2) = '06' then Week1 = (cats(&current_year.,'21'))*1;
else if substr(put(&mth_code.,6.),5,2) = '07' then Week1 = (cats(&current_year.,'25'))*1;
else if substr(put(&mth_code.,6.),5,2) = '08' then Week1 = (cats(&current_year.,'30'))*1;
else if substr(put(&mth_code.,6.),5,2) = '09' then Week1 = (cats(&current_year.,'34'))*1;
else if substr(put(&mth_code.,6.),5,2) = '10' then Week1 = (cats(&current_year.,'38'))*1;
else if substr(put(&mth_code.,6.),5,2) = '11' then Week1 = (cats(&current_year.,'43'))*1;
else if substr(put(&mth_code.,6.),5,2) = '12' then Week1 = (cats(&current_year.,'47'))*1;
else Week1 = 0;
%MEND;

%MACRO Week2;
if substr(put(&mth_code.,6.),5,2) = '01' then Week2 = (cats(&current_year.,'06'))*1;
else if substr(put(&mth_code.,6.),5,2) = '02' then Week2 = (cats(&current_year.,'10'))*1;
else if substr(put(&mth_code.,6.),5,2) = '03' then Week2 = (cats(&current_year.,'14'))*1;
else if substr(put(&mth_code.,6.),5,2) = '04' then Week2 = (cats(&current_year.,'19'))*1;
else if substr(put(&mth_code.,6.),5,2) = '05' then Week2 = (cats(&current_year.,'23'))*1;
else if substr(put(&mth_code.,6.),5,2) = '06' then Week2 = (cats(&current_year.,'27'))*1;
else if substr(put(&mth_code.,6.),5,2) = '07' then Week2 = (cats(&current_year.,'32'))*1;
else if substr(put(&mth_code.,6.),5,2) = '08' then Week2 = (cats(&current_year.,'36'))*1;
else if substr(put(&mth_code.,6.),5,2) = '09' then Week2 = (cats(&current_year.,'40'))*1;
else if substr(put(&mth_code.,6.),5,2) = '10' then Week2 = (cats(&current_year.,'45'))*1;
else if substr(put(&mth_code.,6.),5,2) = '11' then Week2 = (cats(&current_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;

---

[Tom]

Why write a macro when you can use a formula?

This seems to match your logic.

180  data test;
181    do month=1 to 12 ;
182      date=mdy(month,1,2023);
183      format date date9.;
184      week1 = put(date-7,weeku9.) ;
185      week1 = substr(compress(week1,'W'),1,6);
186      week2 = put(intnx('month',date,1,'b')+7,weeku9.);
187      week2 = substr(compress(week2,'W'),1,6);
188      put month= date= week1= week2= ;
189    end;
190  run;

month=1 date=01JAN2023 week1=202252 week2=202306
month=2 date=01FEB2023 week1=202304 week2=202310
month=3 date=01MAR2023 week1=202308 week2=202314
month=4 date=01APR2023 week1=202312 week2=202319
month=5 date=01MAY2023 week1=202317 week2=202323
month=6 date=01JUN2023 week1=202321 week2=202327
month=7 date=01JUL2023 week1=202325 week2=202332
month=8 date=01AUG2023 week1=202330 week2=202336
month=9 date=01SEP2023 week1=202334 week2=202341
month=10 date=01OCT2023 week1=202339 week2=202345
month=11 date=01NOV2023 week1=202343 week2=202349
month=12 date=01DEC2023 week1=202347 week2=202401

[Lkrem]

Thanks all for the suggestions! What I am trying to do is create a macro that calculates the selected weeks for each monthly time period so I don't need to change them every month. The goal is to only have to update the 4 at the top:

%let mth_code = 202301;
%let current_year = 2023;
%let prev_year = 2022;
%let next_year = 2024;

The weekly pull is directly from a data warehouse.

I will see if I can try and work some of these ideas into my code!

Thanks!

[Patrick]

Based on your code it appears you don't fully grasp yet how to work with SAS Date values and all the advantages this brings. 

Week numbers are unfortunately one of the trickier things to work with because there are different ways for deriving these - like does a week start on Sunday or Monday, what defines when the first week of a year starts, does the count start with 0 or with 1?

For your real data: What date range do you actually try to select? Would there be another variable in your data that we could use to define a range like:

From a given date select all rows with a date one week back and 5 weeks into the future.

Please describe in words what you have and how you want to select the data. Ideally also provide sample data representative for your real data.

If there is not other date (string) we can use than yyyyww then please explain in detail how the week number gets determined (...and there can be 53 weeks in a year).