BookmarkSubscribeRSS Feed
madix
Obsidian | Level 7

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

17 REPLIES 17
Kurt_Bremser
Super User

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
Obsidian | Level 7

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?

ChrisHemedinger
Community Manager

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

 

 

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
Kurt_Bremser
Super User

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

PeterClemmensen
Tourmaline | Level 20

Like this?

 

data want;
set have;
where date=intnx('week.2',date,0,'end');
run;
madix
Obsidian | Level 7

Thanks a lot! it finally works!

madix
Obsidian | Level 7

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

ChrisHemedinger
Community Manager

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'); 
It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
madix
Obsidian | Level 7

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
Obsidian | Level 7

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

madix
Obsidian | Level 7

 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
Super User

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
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 17 replies
  • 5141 views
  • 12 likes
  • 4 in conversation