Hello everybody!
To automate a program, i need to update a set of data, so each month the date should be fixed every first friday of the current month,
For example, today we're on november 7, the program must be written this way:
Data Tab_fin; Set Tab_INIT; where Date= '03NOV2017'd; Run;
How can i automate the date, so i won't need to change it every month?
i tried this code:
%let datej =%sysfunc(intnx(week.6,%sysfunc(intnx(MONTH,%sysfunc(today()),0)),1),DATE9.); Data test_&datej; set metiers ( keep=matricule dtmaj country salary code); where code='002' and dtmaj=&datej; run;
and i got this error message:
NOTE: Line generated by the macro variable "DATEJ".
64 03NOV2017
-------
22
76
ERROR: Syntax error while parsing WHERE clause.
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=,
<>, =, >, >=, AND, EQ, GE, GT, LE, LT, NE, OR, ^=, |, ||, ~=.
ERROR 76-322: Syntax error, statement will be ignored.
65 run;
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.TEST_03NOV2017 may be incomplete. When this step was stopped there
were 0 observations and 4 variables.
WARNING: Data set WORK.TEST_03NOV2017 was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.72 seconds
cpu time 0.02 seconds
NOTE: Remote submit to SERVINF.SASBPU93 complete.
Can someone please help me?
@Reeza it works perfectly!! thank you so much!!
Well, I would do it in two steps:
1) get first day of month
2) add days to get to friday
So something like:
%let dt=12MAR2017; data want; indt="&dt."d; /* Move indt to first of month */ indt=intnx('month',indt,0,'b'); /* Add days until Friday which is day 6*/ /* Remove 1 day if its Saturday as this happens after Friday */ if weekday(indt) then indt=indt-1; indt=indt + (6-day(indt)); format indt date9.; run;
Done it a bit long winded so you could see the workings.
The INTNX function should be able to produce the first Friday of each month.
@RW9 thank you for your answer i tried ur code but it gives me 6 october in spite of 3 november
and even though i change the month it gives me everytime the six day of every month :s
Despite the complexity of your formula, perhaps you have already found a way to retrieve the first Friday. However, the problem lies in how you are using it:
where code='002' and dtmaj=&datej;
The macro variable resolves, giving you this invalid SAS statement:
where code='002' and dtmaj=03NOV2017;
I think the only fix you need is to generate a n acceptable SAS statement:
where code='002' and dtmaj="&datej"d;
@Marwa_Se, you had a syntax in your code and you asked for:
To automate a program, i need to update a set of data, so each month the date should be fixed every first friday of the current month,
For the 1st issue, see @Astounding answer.
For the 2nd issue, I would make a slight change to @RW9 posted code:
data want;
indt=today(); /* <<< get today's date >>> */
/* Move indt to first of month */
indt=intnx('month',indt,0,'b');
/* Add days until Friday which is day 6*/
/* Remove 1 day if its Saturday as this happens after Friday */
if weekday(indt) then indt=indt-1;
indt=indt + (6-day(indt));
format indt date9.;
run;
then you don't need to supply the date each time before run.
@Shmuel thank you for ur help! i accepted Reeza's solution, it works good 🙂 thank you anyway!
Your main error is not using a date literal in the WHERE statement.
where code='002' and dtmaj="&datej"d;
But I think your algorithm is off. What day to you want when the first of the month is a Friday? Like it was in September 2017?
Your current algorithm will yield result of 08SEP2017.
556 %let today=20SEP2017; 557 %put &=today; TODAY=20SEP2017 558 %let datej =%sysfunc(intnx(week.6,%sysfunc(intnx(MONTH,"&today"d,0)),1),DATE9.); 559 %put &=datej; DATEJ=08SEP2017
So you could subtract one from the first day of the month.
561 %let datej =%sysfunc(intnx(week.6,%sysfunc(intnx(MONTH,"&today"d,0))-1,1),DATE9.); 562 %put &=datej ; DATEJ=01SEP2017
Or find the last day of the previous month.
564 %let datej =%sysfunc(intnx(week.6,%sysfunc(intnx(MONTH,"&today"d,-1,e)),1),DATE9.); 565 %put &=datej ; DATEJ=01SEP2017
Use NWKDOM instead.
Returns the date for the nth occurrence of a weekday for the specified month and year.
Data Tab_fin;
Set Tab_INIT;
where Date= NWKDOM(1, 6, month(today)), year(today()) );
Run;
@Reeza it works perfectly!! thank you so much!!
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 25. 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.