Contributor
Posts: 27

# get the last day of a week

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

Super User
Posts: 10,574

## Re: get the last day of a week

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;``````
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 27

## Re: get the last day of a week

Posted in reply to KurtBremser

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?

Community Manager
Posts: 3,462

## Re: get the last day of a week

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

Super User
Posts: 10,574

## Re: get the last day of a week

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
PROC Star
Posts: 1,401

## Re: get the last day of a week

[ Edited ]

Like this?

`data want; set have; where date=intnx('week.2',date,0,'end');run;`
Contributor
Posts: 27

## Re: get the last day of a week

Thanks a lot! it finally works!

Contributor
Posts: 27

## Re: get the last day of a week

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

Community Manager
Posts: 3,462

## Re: get the last day of a week

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'); ``````
Contributor
Posts: 27

## Re: get the last day of a week

Posted in reply to ChrisHemedinger

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

Contributor
Posts: 27

## Re: get the last day of a week

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

Super User
Posts: 10,574

## Re: get the last day of a week

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 27

## Re: get the last day of a week

[ Edited ]
Posted in reply to KurtBremser

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.

Super User
Posts: 10,574

## Re: get the last day of a week

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;``````
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,574

## Re: get the last day of a week

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Discussion stats
• 17 replies
• 447 views
• 12 likes
• 4 in conversation