Filtering out values from a column in SAS Enterprise Guide

[Boombox]

Hello everyone,

I am very new to SAS Enterpride Guide and programming in general but have been picking up things quite quickly but I can't seem to resolve this problem, so I am hoping someone can help me with this. 

Basically, I have a table that ranks all the dates of the information that I am working with because I need to generate a weekly report from the days of the most recent week. Generally, I need the dates ranked 1 through 5 (Monday to Friday). However, sometimes there are certain dates that I have been asked to exclude (which I already know what dates those are) and only do a week analysis of the other 4 days. Therefore, I am looking for a way to exclude those dates once they show up (see image below for reference).

So if the date in Table 2 shows up in Table 1 I would like to exclude that whole row. The rank values in the "Desired End Result" table becomes irrelevant to me after filtering out the dates. 

I use SAS Enterpride Guide 8.2

Thank you in advance for the support. 

[PeterClemmensen]

data one;
input rank date :mmddyy10. value;
format date mmddyy10.;
datalines;
1 04/14/2023 12 
2 04/13/2023  8 
3 04/12/2023  6 
4 04/11/2023 17 
5 04/10/2023  2 
;

data two;
input date :mmddyy10.;
format date mmddyy10.;
datalines;
04/14/2023 
04/19/2023 
;

proc sql;
   create table want as
   select * 
   from one
   where date not in (select date from two)
   ;
quit;

[Patrick]

Assuming you're still in the point&click only phase below how you could do this.

Advanced filter - filter clause typed manually

Above works BUT I was only really capable of doing this the point & click way because I knew what SQL code I wanted EG to generate. 

Message is: Point & Click is great but to take things further you need to get into coding both for the SAS data step and SQL.

[Boombox]

Thanks for the reply. It's true that I'm still in the point and click phase so this was extremely helpful.

However it turns out I misunderstood the problem. Turns out the date (04/14/2023) won't show up on the data set so the filter has to grab ranks 1 through 4 instead of ranks 1 through 5 when previous date is in rank 1. Basically when date 04/13/2023 is rank 1 the end result table should only have 4 rows.

[Patrick]

You need to provide some have sample data matching your narrative, show us the desired result and describe the logic to get from have to want as good and detailed as you can.

[Boombox]

So after finally fully understanding the problem I can better explain what is happening and what I am trying to do. Basically what happens is that the way the process flow is set up there is a rank task that will rank the dates in such a way that it will assign rank values in order of recency. For this weekly report that I have to do on Mondays the ranks that I need from the ranked table are only the top 5 ranks. This would be rank 1, the previous Friday, to rank 5, the previous Monday. However, sometimes there will be dates that will not show up on the table (which I know ahead of time) and come Monday when I make my report rather than analyzing the top 5 ranks I only have to use the top 4 ranks. Meaning I would need to only filter from rank 1, previous Thursday, to rank 4, previous Monday. (attempt at a visual representation below)

I was trying to set up an IFN function that would check if a certain date was rank 1 (using a concatenation function with the date and rank columns) that would return 4 else 5 and using the result of the IFN function to setup the filter for Rank =< 'if output'. But not only have I not been able to set it up properly, I'm not sure that is the best or most efficient way. 

Anyways, I appreciate the help!

[Patrick]

@Boombox  If I understand that right then you want to basically select the last 5 days based on the most current date in the data.

I couldn't work out the point-and-click way to get this but it's fairly simple using code.

In SAS a Date value is stored as the count of days since 1/1/1960. So it's just a number with which you can do calculations. 

All the code needs to do is work out the most recent date and then select all dates in the data where max(date)-4 <= date ...could also be: max(date) >= date+4

data one;
  input rank date :mmddyy10. value;
  format date mmddyy10.;
  datalines;
1 04/14/2023 12 
2 04/13/2023  8 
3 04/12/2023  6 
4 04/11/2023 17 
5 04/09/2023  2 
;

proc sql;
  create table want as
  select date, value
  from one
  having max(date)-4 <= date
  order by date descending
  ;
quit;

[Boombox]

@Patrick I think your solution works fine if it were an analysis that I would manually run. But the idea of this is to code it in such a way where the program can understand when to use 5 and when to use 4. If I were just manually doing this every week I would just be conscious of "oh this is the week that I only need to report 4 dates rather than 5" and then I would switch the filter to be RANK<=4 instead of the usual RANK<=5. 

I don't mind having to write/use a program, it's just that I have no idea what the program would look like for this problem. That's why I was trying to setup an if function for the filter that could detect "this time we run the program we only need the 4 most recent dates" unlike normally (literally ever other week that isn't very specific times) when "this time we run the program we need the 5 most recent dates". Now I'm not saying this is what the solution should be I'm just saying what I was trying (with little success). The goal of this is to have it run automatically. 

I hope this clears any possible confusion.