topic Re: How to automate a date in SAS Programming

How to automate a date

Marwa_Se — Tue, 07 Nov 2017 13:54:58 GMT

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

-------

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?

Re: How to automate a date

RW9 — Tue, 07 Nov 2017 14:25:06 GMT

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.

Re: How to automate a date

PaigeMiller — Tue, 07 Nov 2017 14:29:13 GMT

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=p0syn64amroombn14vrdzksh459w.htm&locale=en#n1wbckrlffgb8jn15eebgjpqm23c

Re: How to automate a date

Astounding — Tue, 07 Nov 2017 14:51:00 GMT

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;

Re: How to automate a date

Shmuel — Tue, 07 Nov 2017 15:37:43 GMT

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

Re: How to automate a date

Tom — Tue, 07 Nov 2017 16:09:48 GMT

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

Re: How to automate a date

Reeza — Tue, 07 Nov 2017 16:23:14 GMT

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;

Re: How to automate a date

Marwa_Se — Wed, 08 Nov 2017 07:28:36 GMT

@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

Re: How to automate a date

Marwa_Se — Wed, 08 Nov 2017 07:30:07 GMT

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

Re: How to automate a date

Marwa_Se — Wed, 08 Nov 2017 07:31:39 GMT

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

Re: How to automate a date

Reeza — Wed, 08 Nov 2017 15:48:54 GMT

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