turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- get the last day of a week

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-10-2017 07:01 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to madix

10-10-2017 07:04 AM

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

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to KurtBremser

10-10-2017 07:21 AM

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?

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to madix

10-10-2017 07:26 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to madix

10-10-2017 07:40 AM

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

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to madix

10-10-2017 07:05 AM - edited 10-10-2017 07:06 AM

Like this?

data want;

set have;

where date=intnx('week.2',date,0,'end');

run;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to draycut

10-10-2017 08:41 AM

Thanks a lot! it finally works!

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to madix

10-10-2017 11:34 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to madix

10-10-2017 12:06 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ChrisHemedinger

10-11-2017 08:34 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to madix

10-11-2017 08:39 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to madix

10-11-2017 08:44 AM

So you mean to have the last *work*day 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

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to KurtBremser

10-11-2017 08:53 AM - edited 10-11-2017 09:01 AM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to madix

10-11-2017 09:14 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to madix

10-11-2017 09:34 AM

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