Automation of Weekday Function for Multiple Months

[Yakatan]

I need to automate a number of dates and create these as macro variables moving forward. I have managed to create the macro variables to create the start date of everymonth. However, i then need to create a different value based on the day of the week the previous month has fallen on.I have the following so far;

%macro testmacro;
data test1;
%let start_dt = '01jun2020'd;
%let add = (9 8 7 6 5 11 10);

do i=0 to 12;
varname = catt('month',put(i,z2. -l));
varvalue=intnx('month',"start_dt"d,+1*i,'b');
format var_value date9.;
call symputx(varname,put(varvalue,date9.) 'g');

%let trialdate = %sysfunc(intnx(month,&start_dt,+1*i,b));
%let trialdate2 = %eval(&trialdate+%scan(&add,%sysfunc(weekday(&trialdate))));

output;
end;
run;
mend;
%testmacro;

The values of add relate to the value i need to increment the number of days by for the next month. e.g. if weekday = 1 then i need to increment by a value of 9 days.

i get the following error;

ERROR: Argument3 to function INTNX referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number.

Although i think even when i have resolved this error it isnt going to calculate for 12 months it will only calculate for 1month?

[PaigeMiller]

There are many problems in your code, and I also don't understand this part:

i then need to create a different value based on the day of the week the previous month has fallen on

Please provide a better explanation.

Your code tries to mix and match DATA step and macro, and while it can be done this is never easy, and in your case, there are many mistakes. So, macro variables created inside a DATA step (such a &trialdate) cannot be used later in that data step. Also, %SYSFUNC is a macro function, it cannot access data step variables such as variable I, it can only access macro variables.

What are you trying to do with +1*i ??? Isn't that the same as i ?

So things brings me back to my first comment, it isn't clear to me what you are trying to do, and so a much clearer explanation is requested, so we can move this forward.

Lastly, from now on, when you have errors in the log, show us the entire log for that DATA step or that PROC, every single line in the LOG for that DATA step or PROC, every single character in the LOG for that DATA step or PROC. Do not show us a small portion of the log, as you have done. Please consider this mandatory in the future.

[Yakatan]

I am multiplying by 1*i as i want to calculate this for the whole range of months including within the do step. So +1*i (if i = 3) it gives me the value for the 4th month forward from 01Jun2020.

I will try and explain in more detail with a worked example.

01jul2020 fell on a Friday - i then need to increment the date an incremental 11 days from the start of this month. I need to create this for the subsequent 12months  after Jun2020. Similarly, 1Aug2020 fell on a Saturday, so i need to increment this by 10 days. I could use an IF statement with multiple logic to create these values, but as I am looking at 12 months with different treatment dependant upon which day of the month the first day fell on i was trying to avoid this. The starting point in my data is 01Jun2020, so i wanted this to be the only hardcoded code and then automate everything off of this date.

[PaigeMiller]

Thank you for the explanation.

I am multiplying by 1*i as i want to calculate this for the whole range of months including within the do step. So +1*i (if i = 3) it gives me the value for the 4th month forward from 01Jun2020.

Maybe you mean i+1, not +1*i

According to SAS and according to calendars that I have, 01JUN2020 is a Monday, so this adds 8 days to Monday, and does the correct thing for other months as well. My calendar also shows 01JUL2020 as Wednesday, not Friday as you said. You can see that no macros or macro variables are needed, and this is all done in a single data step.

data test1;
start_dt = '01jun2020'd;
array add(7) _temporary_ (9 8 7 6 5 11 10);
do i=0 to 12;
    first_day_of_month=intnx('month',start_dt,i,'b');
    day_of_week=weekday(first_day_of_month);
    desired_day=first_day_of_month+add(day_of_week);
    output;
end;
format start_dt first_day_of_month desired_day date9.;
run;

[Yakatan]

This gives me exactly what i need! The reason why i was trying to create a macro though, is because later on in my process i am going to need to reference these dates. Sorry as i dont think i explained that particularly well.

E.g.

data have;
input id first_day_of_month :ddyymm10. check_date :ddyymm10.;
datalines;
1 01/06/2020 04/06/2020
2 01/07/2020 02/07/2020

data want;
set have;

if first_day_of_month = '01Jun2020'd and check_date between '01jun2020'd and '09jun2020'd then flag = 'Y';
else if first_day_of_month = '01Jul2020'd and check_date between '01jul2020'd and '07jul2020'd then flag = 'Y';

run;

I was hoping to be able to get something like the below;

data iteration;

do i=0 to 12;

     if first_day_of_month = &datei. and check_date between &datei. and &date2_i. then flag = 'Y';
run;

I know i havent created the variables &datei. and date2_i, but i want them to resolve to the values created in the iterative step above where we have 12 values of i with 12 different dates.

[PaigeMiller]

---

@Yakatan wrote:

This gives me exactly what i need! The reason why i was trying to create a macro though, is because later on in my process i am going to need to reference these dates. Sorry as i dont think i explained that particularly well.

E.g.

data have;
input id first_day_of_month :ddyymm10. check_date :ddyymm10.;
datalines;
1 01/06/2020 04/06/2020
2 01/07/2020 02/07/2020

data want;
set have;

if first_day_of_month = '01Jun2020'd and check_date between '01jun2020'd and '09jun2020'd then flag = 'Y';
else if first_day_of_month = '01Jul2020'd and check_date between '01jul2020'd and '07jul2020'd then flag = 'Y';

run;

I was hoping to be able to get something like the below;

data iteration;

do i=0 to 12;

     if first_day_of_month = &datei. and check_date between &datei. and &date2_i. then flag = 'Y';
run;

I know i havent created the variables &datei. and date2_i, but i want them to resolve to the values created in the iterative step above where we have 12 values of i with 12 different dates.

---

DATA steps in SAS can work with data, there's no real need to store data in macro variables for use in a DATA step. In my opinion, you are going down a very difficult path if you are choosing macro solutions as your first choice.

When providing us with SAS code that illustrates something that you want, you should at least eliminate syntax errors and typographical errors in your code.

 68         
 69         data have;
 70         input id first_day_of_month :ddyymm10. check_date :ddyymm10.;
                                         _________             _________
                                         485                   485
 NOTE 485-185: Informat DDYYMM was not found or could not be loaded.

Here is a solution that does not involve macros or macro variables.

data test1;
	start_dt = '01jun2020'd;
	array add(7) _temporary_ (9 8 7 6 5 11 10);
	do i=0 to 12;
	    first_day_of_month=intnx('month',start_dt,i,'b');
	    day_of_week=weekday(first_day_of_month);
	    desired_day=first_day_of_month+add(day_of_week);
	    output;
	end;
	format start_dt first_day_of_month desired_day date9.;
run;

data have;
input id first_day_of_month :ddmmyy10. check_date :ddmmyy10.;
format check_date date9.;
datalines;
1 01/06/2020 04/06/2020
2 01/07/2020 02/07/2020
3 01/08/2020 14/08/2020
;

proc sort data=have;
    by first_day_of_month;
run;

data want;
     merge have(in=in1) test1(drop=i day_of_week start_dt);
     by first_day_of_month;
     if first_day_of_month<=check_date<=desired_day then flag=1;
     else flag=0;
     if in1;
run;

[Yakatan]

Thank you. I understand what you are saying about this being able to be done in a datastep and i appreciate the solution. The reason for me wanting these dates to be able to be included in a macro which i can reference is later on in my process i am going to need to apply further criteria at different points.

I thought it would be better practice to be able to reference a variable like &datei instead of having to hard code dates within my code. e.g. &date12. would resolve to "01Jun2021" and &desired_date12. would resolve to "08Jun2021"

[PaigeMiller]

---

@Yakatan wrote:
Thank you. I understand what you are saying about this being able to be done in a datastep and i appreciate the solution. The reason for me wanting these dates to be able to be included in a macro which i can reference is later on in my process i am going to need to apply further criteria at different points.

---

You can use the values in a SAS data set later as well.

But I think its time for you to explain the big picture, the entire usage of this information, start to end. We can't write code for some unspecified future use.

[Yakatan]

If there is a way that i can reference dates without having to use a macro then i am more than happy for that. I wanted to avoid a situation where i have any hardcoded dates within my code. The way i was trying to approach that was to create the equivalent of 12 macro dates that would resolve to their respective values.

If i wanted to change my starting point from '01jun2020' to '01jun2018' the values within those dates would change but the logic would remain the same. As an example further on in my code i need to apply some very simple logic if the dates are after the 6th iteration. As it currently stands when i reach that point my code would be;

if first_day_of_month >= "01dec2020"d then do;
...
end;

if first_day_of_month >= "01mar2021"d then do; 
...
end;

There are numerous instances within my code where a reference to a hardcoded date is made, in the two examples above these dates relate to the 6th and 9th iteration after the start_dt. If my start_dt changes i would still want the same logic to apply to the 6th and 9th iteration. Which is way i was perhaps incorrectly thinking the best way to appraoch this would be to create 12 date macro variables which would automatically adjust to any change in start_dt and not rely on any manual changes to the code apart from the change to start_dt.

[PaigeMiller]

So you can see in the code I provided that the IF statement doesn't use macro variables. It uses data stored in a data set. In your future logic, if you can create it properly, you could also use data stored in a data set.

But this explanation is still too general for me. I don't feel you have described the future use at all. I don't know what future logic you are thinking of. I don't see where you are going or why you still think you need macro variables. I don't know what 6th iteration or 9th iteration means.

[Yakatan]

Yes, so your solution has allowed me to get to the value of the variables i need, what i was now wanting to do was to reference those values at a later step.

I am going to be applying my logic to a dataset that has more than 10million rows, so i didnt want to have to create additional data if i could help it, hence why i thought it would be better to use a macro variable. Similarly i may be require to have numerous start dates which mean i would be potentially creating quite a lot of additional data. When i speak about the 6th iteration i was referring to the output creating from your solution when i=6.

[FreelanceReinh]

---

@Yakatan wrote:
I am going to be applying my logic to a dataset that has more than 10million rows, so i didnt want to have to create additional data if i could help it, (...)

---

There are ways to pull data from a dataset containing the dates (date1, date2, ...) temporarily into a DATA step that is processing the 10-million-rows dataset (let's call it HAVE). One is to create one (or more) datasets with one observation and several variables (here: containing the dates) and retrieve their values with a SET statement in the first iteration of the DATA step (if _n_=1 ...).

Example:

data dates;
input date :date9.;
format date date9.;
cards;
09JUN2020
07JUL2020
11AUG2020
;

proc transpose data=dates out=dates_wide(drop=_name_) prefix=_date;
run;

data want(drop=_:);
set have;
if _n_=1 then set dates_wide;
array d[*] _date:;
/* ... code using d[1], d[2], etc. ... */
run;

Other options include loading the dates directly from dataset DATES into a temporary array or a hash object, whatever is most suitable. Really, there's no need for dozens of macro variables.

[PaigeMiller]

---

@Yakatan wrote:
Yes, so your solution has allowed me to get to the value of the variables i need, what i was now wanting to do was to reference those values at a later step.

I am going to be applying my logic to a dataset that has more than 10million rows, so i didnt want to have to create additional data if i could help it, hence why i thought it would be better to use a macro variable. Similarly i may be require to have numerous start dates which mean i would be potentially creating quite a lot of additional data. When i speak about the 6th iteration i was referring to the output creating from your solution when i=6.

---

I think we are talking around in circles. Everything you say, in my opinion, indicates you need to use DATA steps and not macros.

Using DATA steps as I showed earlier on your very small sample doesn't have to enlarge the size of the resulting data set, you can drop the variables you don't want (in my earlier code this would be variables first_day_of_month and desired_day) and thus the data set does not grow bigger.

[Tom]

Why are you testing two different date ranges and only setting on FLAG variable?
How does the code change when you have three date ranges?

What does it look like when you have all 12 (or is it 13) date ranges?

Are you sure you don't want to just apply a FORMAT to the DATE variable in your dataset?

proc format ;
value month_number
'01JUN2020'd - '09JUN2020'd = 'First month'
'01JUL2020'd - '07JUL2020'd = 'Second month'
....
 ... = 'Twelfth month'
  other = 'Other dates'
;
run;

proc freq data=have;
  tables create_date ;
  format create_date month_number.;
run; 

[FreelanceReinh · Accepted solution]

Maybe the calculation can be further simplified:

data test2;
start_dt = '01jun2020'd;
do i=0 to 12;
  desired_day=intnx('week.3', intnx('month',start_dt,i)-3, 2);
  output;
end;
format start_dt desired_day date9.;
run;