topic Delete data from a daily created dataset based on Date Range in SAS Programming

Delete data from a daily created dataset based on Date Range

dennis_oz — Sat, 26 Dec 2020 22:22:53 GMT

Hi all,

I need your assistance . I am writing data daily into a dataset and distinguishing this with a Date_Field.

I do not want not mare than 10 days data in this dataset. i.e as part of housekeeping I want to delete data older than 10 days from this dataset .

Sample code:

data a;
	format x date9.;
	x=today();

	do i=1 to 15;
		x+1;
		output;
	end;
run;

Output:

In the above case I would like to delete data that is starting from 28- Dec till 01-Jan-2021.

Thanks,

Re: Delete data from a daily created dataset based on Date Range

PaigeMiller — Sat, 26 Dec 2020 23:26:48 GMT

data a;
	format x date9.;
	x=today();
	do i=1 to 15;
		x+1;
		if not ('28DEC2020'd<=x<='01JAN2021'd) then output;
	end;
run;

If you just want 10 observations, then when you go to use data set A, you can use something like this:

proc print data=a(obs=10);

Re: Delete data from a daily created dataset based on Date Range

SASKiwi — Sat, 26 Dec 2020 23:18:26 GMT

If you want just the top 10 dates regardless of the period chosen:

data have;
  format MyDate date9.; 
  do MyDate = '28Dec2020'd to '11Jan2021'd;
    output;
  end;
run;

proc rank data = have out = want1 descending;
  ranks MyDateRank;
  var MyDate;
run;

data want2;
  set want1;
  where MyDateRank <= 10;
run;

Re: Delete data from a daily created dataset based on Date Range

mkeintz — Sat, 26 Dec 2020 23:25:16 GMT

Using the same data set name for a data set that not only gets new data, but also has old data removed, is dangerous when done in a normal data step - because the removal is likely irreversible. One mistake and erroneously removed data is irretrievable.

One possibility to avoid this risk is to just append new data, and remove none. Then introduce a filter for data-access purposes to access only the last ten days. Let's assume there is dataset file HAVE with data that goes back any number of days, with daily data appended, but no data removed. You could then permanently define a "dataset view" of that dataset file, that will always retrieve only data within the last ten calendar days. The dataset view does not have to be redefined daily - once is enough.

data vhave / view=vhave;
  set have;
  where x-9 >= "&sysdate9"d;
run;

You could then do all your subsequent reporting/analysis tasks using VHAVE, no matter how many updates have been applied to HAVE. That's because the dataset view is nothing more than a pre-defined process. It doesn't write data to disk, but it looks like a normal dataset file to any subsequent procedure that accesses it. That is, you could do something like this:

proc print data=vhave;
run;

BTW, &SYSDATE9 is an automatic macrovar. To see what it looks like issue the macro statement %PUT &=SYSDATE9.;

Of course, with daily updates, the original dataset HAVE would probably need to be pruned from time-to-time. But that could be done in a controlled process that included backup or snapshot files to protect against inadvertent data destruction.

Re: Delete data from a daily created dataset based on Date Range

singhsahab — Sun, 27 Dec 2020 07:21:29 GMT

data a;
	format x date9.;
	x=today();

	do i=1 to 15;
		x+1;
		output;
	end;
run;

%macro delete_(indata=,startdate=,enddate=);
proc sql;
delete from &indata where x between &startdate. and &enddate.;
quit;
%mend;
%delete_(indata=a,startdate='28DEC2020'D,enddate='01JAN2021'd);

proc print data=a;
run;