get the last day of a week

[madix]

Hello,

If anyone could help me it will be great!

My problem is the following one:

I have a table (one column is date and the other one is rate). I would like to keep only the line with the last day of the week.

here is my table.

If anyone could help me.. thank you

[Kurt_Bremser]

Use the weekday() function, either in a where condition or a subsetting if:

data want;
set have;
where weekday(date) = X;
/* alternatively: */
if weekday(date) = X;
/* insert a suiting number for X, according to what you define as the last day of the week */
run;

[madix]

Thank you for your quick reply.

I have a problem. Each time I try the function weekday() on a date like 10/10/2017, it gives me the number 6. Do you know why?

[ChrisHemedinger]

To specify a Date value, you have to express it with a date literal:

a = weekday('02Oct2017'd);

In your example, SAS evaluates the expression as 10 divided by 12 divided by 2017 (10/12/2017).

[Kurt_Bremser]

---

@madix wrote:

Thank you for your quick reply.

I have a problem. Each time I try the function weekday() on a date like 10/10/2017, it gives me the number 6. Do you know why?

---

Ouch. When you do this:

a = weekday(11/02/2017);

you create a value of 10 divided by 2 divided by 2017, which is considerably less than 1, so you get the date of SAS day zero, which was 01jan1960, a Friday (weekday 6).

[PeterClemmensen]

Like this?

data want;
 set have;
 where date=intnx('week.2',date,0,'end');
run;

[madix]

Thanks a lot! it finally works!

[madix]

Still in my table, I wonder how to get a line where there is the last day of a month. Can someone help me ?

 For example in this table, I would like to keep the line where there is 03/31/2005 and all the other

[ChrisHemedinger]

The INTNX function has a treasure trove of these types of calculations.

/* true when the date is the same as EOM date */
where date = intnx('month', date, 1, 'e'); 

[madix]

Thank you for your reply.

The thing is that I would like to keep only the line with the last day of the month in my date. In my own case I will have to keep the line with the following date:

03/31/2005

04/29/2005

05/31/2005

[madix]

The solution works well, but it avoid the date 04/29/2005 which is in my list. It doesn't take my last date of each month that I have

[Kurt_Bremser]

So you mean to have the last workday of a month, excluding Saturdays and Sundays? What about holidays?

[madix]

 I realise my problem is a bit more complicated than I thought:

1/ I don't need the last day of a week but I need the last day of the week which is in my set of data.

So sometimes, instead of taking friday, I will take thursday because friday is not the last day of the week I have in my data.

Saturday and sunday doesn't matter here I would say because I don't have any data at those date.

2/Then I would like to do the same by taking the last day of the month in my data and not the last day of a month in general.

[Kurt_Bremser]

First, assign a group variable to your data that references the week; you can use the week() function for that.

Then, find the max(date) within each week.

Example:

data have;
format mydate yymmddd10.;
x = 0;
do mydate = today() - 20 to today();
  x + 1;
  if weekday(mydate) not in (6,7) then output;
end;
run;

proc sql;
create table want as
select
  mydate, x, week(mydate) as week
from have
group by week
having mydate = max(mydate);
quit;

[Kurt_Bremser]

As an addendum to my previous post: if your data spans more than one year, you need to create a compound variable (year and week number) to reliably identify a week.