Create rolling 12 months of datasets from large dataset contain 15 months of data

[dkcoop]

I have a macro that creates 15 datasets by date from one dataset that contains 15 months of data indexed by datekey in the format yyyymmdd.  It is a simple set statement with a where clause. Because I'm rewriting this code from someone else, there are already 15 individual datasets in mylib.  I only want 12 rolling months of datasets.  In the code I've attached, I created all 15 datasets again because I thought that would be easiest.  What I'd really like going forward is to read 12 months of data from the large dataset (which I can by adjusting _N_) and delete the earliest mylib dataset that's already out there.

/*date_key is in format yyyymmdd.*/
proc sql;
create table testdata as
select distinct date_key from mylib.mth_datasets;
quit;
/*the most recent date will be id 1*/
proc sort data=testdata out=unique nodupkey;
	by descending date_key:
run;
data unique;
	set unique;
	id=_N_;
data _null_;
	set unique end=last;
	if last then call symput('N', put(id, 3.));
%let i = 1
%macro getdata (dsn);
%do i = 1 %to &N;
data _null_;
	set unique;
	call symputx(cats("date_nbr"),put(intnx('month',today(),-&i,'E'), yyyymmdd.));
run;
data mylib.mth_datasets_&date_key;
	set &dsn;
	where date_key = &date_nbr;
%end;
%mend getdata;
%getdata(mylib.mth_datasets)

[PeterClemmensen]

So you want to create 12 individual datasets - one for each of the preceding months, correct?

If so, there are better ways to do this.

[dkcoop]

I'm sure there ARE better ways of doing this than what I am doing.  While searching for solutions I've managed to patch together a program that does what I want but if there's a better way I'd love to know it!  

[Kurt_Bremser]

I inserted your code into a code box for better readability.

This

data unique;
	set unique;
	id=_N_;
data _null_;
	set unique end=last;
	if last then call symput('N', put(id, 3.));

can be easier done by retrieving the number of obs from DICTIONARY.TABLES:

proc sql noprint;
select nobs into :n from dictionary.tables
where libname = "WORK" and memname = "UNIQUE";
quit;

To use a last date to create a cutoff from a large dataset, do this:

proc sql noprint;
select intnx('month',max(date_key),-12,'b') into : cutoff
from mylib.mth_datasets;
quit;

data want;
set mylib.mth_datasets;
where date_key ge &cutoff.;
/* other code */
run;

Big business-class hint: make it a habit to always end your steps with a RUN statement. Sooner or later an "open" step will cause havoc with the timing of global or macro statements.

[dkcoop]

I tried 

select intnx('month',max(date_key),-12,'b') into : cutoff

But am getting Note "invalid arguument to function intnx missing values may be generated and nothing is returned.  Is it the max function?

[Kurt_Bremser]

I am, of course, assuming that your date_key variable contains valid SAS dates, which means it is numeric and has a YYMMDDN8. format attached.

With SAS dates, the code works, see here:

data have;
input date_key yymmdd10.;
format date_key yymmdd10.;
datalines;
2021-09-20
2021-08-31
;

proc sql noprint;
select intnx('month',max(date_key),-12,'b') into : cutoff
from have;
quit;

data _null_;
cutoff = &cutoff;
put cutoff yymmdd10.;
run;

Log excerpt:

 82         data _null_;
 83         cutoff = &cutoff;
 84         put cutoff yymmdd10.;
 85         run;
 
 2020-09-01