CODEWITHSUNDEEP
sqlsas
Alastair
Alastair

Reputation: 17

How to calculate the number of days of first week of the month in SAS.if we consider Saturday as first week day

I am using intnx function.

date1=intnx('qtr',"01APR2021"d,0,'b');

I am thinking of following algo-

1.Check if last month's last day is not Friday.

2.Calculate the number of days till Friday appears(Current Month).

Can anyone help me with this?

Upvotes: 0

Views: 465

Answers (3)

Reeza
Reeza

Reputation: 21274

  1. Find the first of the month (INTNX() or MDY())
  2. Find the first friday (day=6) of the month (NWKDOM())
  3. Subtract

Assume month and year are provided.

data want;
  year = 2020;
  month = 1;
  start_date = mdy(month, 1, year);
  first_friday = nwkdwom(1, 6, month, year);
  length_week = start_date - first_friday + 1;

  *Toms correction:;
  length_week2 = day(nwkdom(1,6,month(date),year(date));
run;

Upvotes: 1

Tom
Tom

Reputation: 51581

Sounds like you want to know the date of the first Friday in the month. And hence the day of the month of the first Friday in the month.

So let's start with a dataset that has a month that starts on each day of the week.

data example;
  input date :date9.;
  format date date9.;
  dow = weekday(date);
  downame=put(date,downame.-l);
cards;
01OCT2000
01MAY2000
01FEB2000
01MAR2000
01JUN2000
01SEP2000
01JAN2000
;

Now we can use the INTNX() function with the WEEK interval to find the date of the first Friday. We could then use the DAY() function to find the day of the month for that Friday and hence how many days are in the first week of the month.

To get the first Friday find the Saturday at the end of the week containing the second day of the month and subtract one to move back to Friday.

data want;
  set example;
  friday = intnx('week',date+1,0,'e')-1 ;
  want = day(friday);
  want_name=put(friday,downame.-l);
  format friday date9.;
run;

Results:

Obs         date    dow    downame         friday    want    want_name

 1     01OCT2000     1     Sunday       06OCT2000      6     Friday
 2     01MAY2000     2     Monday       05MAY2000      5     Friday
 3     01FEB2000     3     Tuesday      04FEB2000      4     Friday
 4     01MAR2000     4     Wednesday    03MAR2000      3     Friday
 5     01JUN2000     5     Thursday     02JUN2000      2     Friday
 6     01SEP2000     6     Friday       01SEP2000      1     Friday
 7     01JAN2000     7     Saturday     07JAN2000      7     Friday

To do it with any day in the month use the INTNX() function with MONTH interval to find the first day of the month (and add one to find the second day of the month).

length_week1=day(intnx('week',intnx('month',date,0,'b')+1,0,'e')-1);

Upvotes: 0

Richard
Richard

Reputation: 27508

Use INTNX to compute the 1st of the month, and then WEEKDAY of that to compute the number of days in the first week.

WEEKDAY function results are

             1st-want  1st_want-v2
1 Sunday     1         2
2 Monday     2         3
3 Tuesday    3         4
4 Wednesday  4         5
5 Thursday   5         6
6 Friday     6         7
7 Saturday   7         1

Depending on what you want, (suppose the 1st is a Saturday, do you want 1 or 7 for your result?) For 1, you will need to perform additional calculations.

Example:

Presume if first is a Saturday you want 7 days in first week result.

data have;
  call streaminit (2021);
  do _n_ = 0 to 23;
    date = intnx('month', '01-jan-2019'd, _n_) + rand('integer', 27);
    output;    
  end;
  format date date11.;
run;

data want;
  set have;
  month_1st = intnx('month', date, 0);
  sas_weekday_1st = weekday(month_1st);

  result = sas_weekday_1st;
  result_v2 = mod(sas_weekday_1st,7) + 1;

  format month_1st date11.;
run;

Upvotes: 1

Related Questions