Structuring Semi Perm Table That Appends and Removes Date Ranges

[b77]

Hey SAS Community,

Newish to SAS Enterprise Guide. 

Looking to create a semi perm table (that can be accessed by multiple users) that will append a table daily with data in a new date range and remove the oldest date range from the table so its constantly in a 90 day date range.  I know someone cannot just simply write code for me, but looking for direction on functions/concepts (proc append, possibly a macro, etc....)  I should be studying.  If this has been answered before, maybe you could help me with even search terms.  I just can't find exactly what I am looking for.

This table will run analysis over date ranges on the data that will inform volume increases daily, weekly, monthly.  Hope that makes sense.

Thanks,

b77

[ballardw]

Appending data should be relatively simple, Proc Append being the first place I would.

Instead of "removing data ranges" from that data set, which is going to require multiple steps, I would suggest that when actually using the data to specify the range of data needed at the time.

You can use WHERE statements or data step options with the function INTNX to select values based on the current system date assuming you have a variable in the data that is an appropriated date. If not, the question would be  why isn't there such. An example of such selection.

data example;
   do datevar = '01JAN2022'd to today();
      somevar= rand('integer',100);
      output;
   end;
   format datevar date9.;
run;


proc print data=example;
   where datevar ge intnx('day',today(),-90);
run;

Or create the complete data set and then use VIEW based on similar WHERE of the main data set.

This approach means you can do a few things that your removed data would have problems with such as:

1) use for any period longer than 90 days

2) rerunning a model for a specific 90 day, or other interval, after the data was removed. It seems than any scheduled process will have issues at some time and if your data is gone you can't get the intended results.

[Patrick]

One option is to create separate daily SAS tables with a date component in the name and a view that includes the tables in the desired date range. Users then access the data view the view.

Whenever you add a new daily table you also replace the view. 

To avoid locking issues: Make sure that you either use filelockwait=<some value> in the target lib for the view or then create the new view first in Work and use an OS move or copy command.

Using a view has two main advantages

1. You can define multiple views for multiple date ranges without data replication

2. You can easily "delete" data. With SAS tables an in-place deletion is only logical which means you still got a file that's continually growing, or you need to fully re-create the table to physically remove rows from it.

Here some self-contained sample code how this view approach could work.

/* create sample data */
data _null_;
  if 0 then set sashelp.class;
  dcl hash h1(dataset:'sashelp.class(obs=1)',hashexp:4);
  h1.defineKey('name');
  h1.defineData(all:'y');
  h1.defineDone();
  do i='01jan2023'd to today();
    h1.output(dataset:cats('work.dailytbl_',put(i,yymmddn8.)));
  end;
run;

/* macro for view creation over daily tables */
%macro create_view(
   in_lib       =
  ,in_tbl_root  =
  ,out_lib      =
  ,out_view     =
  ,from_dt      = 
  ,to_dt        = 
  );
  proc sql noprint;
    select cats("'",pathname(libname),'/',memname,'.sas7bdat',"'")
      into :tbl_list separated by ' '
    from dictionary.tables
    where 
      libname=%upcase("&in_lib") 
      and prxmatch("/^&in_tbl_root._\d{8}$/oi",strip(memname))>0
      and input(scan(memname,-1,'_'),yymmdd8.) between &from_dt and &to_dt
    order by memname
      ;
  quit;

  data &out_lib..&out_view/view=&out_lib..&out_view;
    length __inds $1000;
    format data_date date9.;
    set &tbl_list indsname=__inds;
    data_date=input(scan(__inds,-1,'_'),yymmdd8.);
  run;
%mend;

/* execute macro to create view over last 90 days */
%create_view(
   in_lib       = work
  ,in_tbl_root  = dailytbl
  ,out_lib      = work
  ,out_view     = tbl_90
  ,from_dt      = %sysfunc(sum(%sysfunc(today()),-89))
  ,to_dt        = %sysfunc(today())
  );

/* print view */
proc print data=work.tbl_90;
run;

The set statement in the view uses fully qualified paths to the SAS tables and not <libref>.<table name> syntax. Using fully qualified paths removes the need for a pre-defined libref that points to the path where the daily tables reside.

[b77]

Thank you so much for this 🙂