DATA Step, Macro, Functions and more

get the last day of a week

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

 

sashelp.PNG

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

sashelp.PNGsashelp2.PNG

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?

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


madix wrote:

sashelp.PNGsashelp2.PNG

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
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 ?

sashelp.PNG

 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

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

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
Ask a Question
Discussion stats
  • 17 replies
  • 447 views
  • 12 likes
  • 4 in conversation