topic Re: get the last day of a week in SAS Programming

get the last day of a week

madix — Tue, 10 Oct 2017 11:01:25 GMT

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

Re: get the last day of a week

Kurt_Bremser — Tue, 10 Oct 2017 11:04:50 GMT

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;

Re: get the last day of a week

PeterClemmensen — Tue, 10 Oct 2017 11:06:27 GMT

Like this?

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

Re: get the last day of a week

madix — Tue, 10 Oct 2017 11:21:18 GMT

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?

Re: get the last day of a week

ChrisHemedinger — Tue, 10 Oct 2017 11:26:12 GMT

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

Re: get the last day of a week

Kurt_Bremser — Tue, 10 Oct 2017 11:40:06 GMT

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

Re: get the last day of a week

madix — Tue, 10 Oct 2017 12:41:23 GMT

Thanks a lot! it finally works!

Re: get the last day of a week

madix — Tue, 10 Oct 2017 15:34:34 GMT

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

Re: get the last day of a week

ChrisHemedinger — Tue, 10 Oct 2017 16:06:31 GMT

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');

Re: get the last day of a week

madix — Wed, 11 Oct 2017 12:34:40 GMT

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

Re: get the last day of a week

madix — Wed, 11 Oct 2017 12:39:40 GMT

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

Re: get the last day of a week

Kurt_Bremser — Wed, 11 Oct 2017 12:44:18 GMT

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

Re: get the last day of a week

madix — Wed, 11 Oct 2017 13:01:22 GMT

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.

Re: get the last day of a week

Kurt_Bremser — Wed, 11 Oct 2017 13:14:32 GMT

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;

Re: get the last day of a week

Kurt_Bremser — Wed, 11 Oct 2017 13:34:08 GMT

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.

Re: get the last day of a week

madix — Wed, 11 Oct 2017 13:39:16 GMT

I don't understand this part:

do mydate = today() - 20 to today();

why do we use this?

should I replace today() by something?

Re: get the last day of a week

madix — Wed, 11 Oct 2017 14:03:50 GMT

my date is taking many year like 2000 to 2017 by the way

Re: get the last day of a week

Kurt_Bremser — Wed, 11 Oct 2017 18:47:28 GMT

@madix wrote:

I don't understand this part:

do mydate = today() - 20 to today();

why do we use this?

should I replace today() by something?

I just created 20 dates for testing, that's all.