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;
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?
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.
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.
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("¤t_year", put(trim(substr("&mth_code", 5, 2)), week_one_fmt.));
week2 = catt("¤t_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(¤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;
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
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!
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).
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: