BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Marwa_Se
Obsidian | Level 7

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? Woman Frustrated

1 ACCEPTED SOLUTION

Accepted Solutions
Marwa_Se
Obsidian | Level 7

@Reeza it works perfectly!! thank you so much!!

View solution in original post

10 REPLIES 10
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

PaigeMiller
Diamond | Level 26

The INTNX function should be able to produce the first Friday of each month.

http://documentation.sas.com/?cdcId=pgmmvacdc&cdcVersion=9.4&docsetId=lefunctionsref&docsetTarget=p0...

--
Paige Miller
Marwa_Se
Obsidian | Level 7

@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

Astounding
PROC Star

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;

Shmuel
Garnet | Level 18

@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.

Marwa_Se
Obsidian | Level 7

@Shmuel thank you for ur help! i accepted Reeza's solution, it works good 🙂 thank you anyway!

Reeza
Super User

@Marwa_Se wrote:

@Shmuel thank you for ur help! i accepted Reeza's solution, it works good 🙂 thank you anyway!


lol, actually you accepted your own answer but I appreciate it 😉

Tom
Super User Tom
Super User

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 
Reeza
Super User

Use NWKDOM instead.

NWKDOM Function

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;
Marwa_Se
Obsidian | Level 7

@Reeza it works perfectly!! thank you so much!!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 3643 views
  • 6 likes
  • 7 in conversation