Delete rows based on date

[aperansi]

I run a report monthly on the 1st day of the month that includes history going back 60 days.

I am trying to delete records from the final data set that is created based on the date. I would like to keep the data for the previous month, but delete any other records that are outside of the month I need them for. Due to the nature of the report, I have to query the results for the past 60 days, as some of the variables require that. Otherwise, I would only run the report for the last 30 days. 

Here is an  Example: If I am running the report on February 1st. Data from December and January will return in the first data set. In my second data step, I would like the Data from December to be deleted,  and only leave the Data from January. I would also need it coded in such a way where this is dynamic.

Can anyone help with this?

[VDD]

do you have sample data that represents your needs?  If you do that would assist in coding your question.

If you don't then you could do this a few different ways.

here is 1: if date < '01JAN2019'date9. then delete.

here s 2: where date > '31DEC2018'date9.;

[aperansi]

Here is a sample of the data you requested. Under the date column, you will see that I have November, December, and January in the results. I ran the report January 1st, and I only want the date for December of 2018 to show. 

I also dont want to hardcode dates in,  and would like this to work dynamically each month if possible.

[ballardw]

Is it actually exactly 60 and 30 days OR from the first of the month two months previous and the first of the previous month?

Also is your date variable in your data a SAS date value (has a format like Date9. or mmddyy10. or similar assigned) or something else?

If the value of your date variable is a SAS date value then you would be looking to use some of the examples below (only one per set though)

data want;
  set have;
  /* dates from the first of the month 2 previous*/
  if date ge intnx('month',today(),-2,'B');
    /* dates from the first of the month 1 previous*/
  if date ge intnx('month',today(),-1,'B');
  /* dates from 60 days previous to today*/
  if date ge intnx('day',today(),-60);
  /* dates from 30 days previous to today*/
  if date ge intnx('day',today(),-30);
run;

The INTNX function creates a date, time or datetime value that is incremented from a given date. There are number of different intervals, the first parameter. The "B" in the first two is get the beginning of the interval. So with month you get the first day of a month.

The IF is a subsetting if and only keeps records that are true for the condition(s) given.

https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/... has a PDF with much information about dates.

[aperansi]

It doesnt have to be exactly 60 days, I just run it that far back so that I include all of the data that I need. But for reporting purposes, the audience looking at this only needs to see the last 30 days.

Also the date variables is currently formatted as yymmd7.

[ballardw]

---

@aperansi wrote:
It doesnt have to be exactly 60 days, I just run it that far back so that I include all of the data that I need. But for reporting purposes, the audience looking at this only needs to see the last 30 days.

Also the date variables is currently formatted as yymmd7.

---

The current format of a date variable makes no difference in the calculations or comparisons.

Did you try testing any of the code example I showed in the other post? It sounds like your audience wants the intnx('day', today(),-30) version if you want 30 days prior to the day the code runs.

[aperansi]

Hey Ballard, I'm not really sure that it worked to be honest. Here is a sample of the dataset that has returned.

To talk through my objective so that I'm making more sense. In this scenario, I ran the report for the last 45 days to capture December and January. I only want Decembers numbers to display, however I'm still showing January. Can you help me figure out how to only show the data for December in this case?

Going forward, when I run this report, The month that I run it in. Say Feb 1, should only include the data/counts for January. Hope that I am making sense, and let me know if I can clarify better. 

Im not sure if this will help, but here are my date variables that I have created to work throughout the report. 

data _null_;
  today=today();
  call symputx('enddate',quote(put(today,yymmddd10.),"'"));
  call symputx('startdate',quote(put(today-45,yymmddd10.),"'"));
run;

data _null_;
  today=dhms(today(),0,0,0);
  call symputx('end_dt',cats(quote(put(today,datetime16.),"'"),'dt'));
  start_dt=intnx('dtday',today,-45,'b');
  call symputx('start_dt',cats(quote(put(start_dt,datetime16.),"'"),'dt'));
run;

%put &=enddate;
%put &=startdate;

Here is the code that I used which you recommended:

data work.ClarityEstimations3;
	set work.ClarityEstimations2;
if date >= intnx('day', today(),-30);
run;

[ballardw]

---

@aperansi wrote:

Hey Ballard, I'm not really sure that it worked to be honest. Here is a sample of the dataset that has returned.

To talk through my objective so that I'm making more sense. In this scenario, I ran the report for the last 45 days to capture December and January. I only want Decembers numbers to display, however I'm still showing January. Can you help me figure out how to only show the data for December in this case?

---

So, again I am asking, what dates do you want? Previously you said 30 or 60 days and now 45?

A basic filter to select dates in a given month would be something like (date below is a variable containing a date value):

Where   month(date)=12 and Year(date)=2018.

or

where   '01Dec2018'd le date le '31Dec2018'd;

or

Where put(date,monyy7.)='DEC2018'

and more.

The first may be more amenable to offset calculations as you only need one date value in the period of interest when it is a month to get month and year.

[aperansi]

After thinking about what I really need, I need to be able to run the report on the 1st of a given month, (Ex: Feb 1), and have it return the data for everything in January. So 30 days is what I believe I need.

The reason I had 45 days was because Im working on this in the middle of the month and im trying to test whether this works right now. 

[Tom]

If you want the data for the PREVIOUS month then you could do something like:

where date between intnx('month',today(),-1,'b') and intnx('month',today(),-1,'e') ;

The INTNX() function calculates a new date value using intervals. The first arg is the interval type, The second the reference value. The third the number of intervals to move. And the last is which day in the interval.

[aperansi]

Do i need an if statement that precedes the where statement you have above? If so, what would that look like.

Not really sure the best way to go about this.

[Tom]

It is not hard to convert the logic of a WHERE statement to an IF statement, once you know how to get the boundary dates for your testing.  But your original description is too sketchy to provide more detailed advice.  It sounds like you might need to filter at multiple points during the process.  You might even need to filter groups of records based on where any records in the group fall into a specific date range.

Can you ask a more specific example of what is confusing you?

[aperansi]

@ballard Can you help me figure this out. I'm stuck and cant get the results to display the way i need them to.