DATA Step, Macro, Functions and more

How to automate a date

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

How to automate a date

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


Accepted Solutions
Solution
‎11-08-2017 02:30 AM
Occasional Contributor
Posts: 12

Re: How to automate a date

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

View solution in original post


All Replies
Super User
Super User
Posts: 9,227

Re: How to automate a date

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.

Respected Advisor
Posts: 2,661

Re: How to automate a date

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
Occasional Contributor
Posts: 12

Re: How to automate a date

@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

Super User
Posts: 6,543

Re: How to automate a date

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;

Trusted Advisor
Posts: 1,827

Re: How to automate a date

[ Edited ]
Posted in reply to Astounding

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

Occasional Contributor
Posts: 12

Re: How to automate a date

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

Super User
Posts: 22,874

Re: How to automate a date


Marwa_Se wrote:

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


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

Super User
Super User
Posts: 7,860

Re: How to automate a date

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 
Super User
Posts: 22,874

Re: How to automate a date

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;
Solution
‎11-08-2017 02:30 AM
Occasional Contributor
Posts: 12

Re: How to automate a date

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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