topic Re: Create rolling 12 months of datasets from large dataset contain 15 months of data in SAS Programming

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

dkcoop — Thu, 23 Sep 2021 04:47:20 GMT

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)

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

PeterClemmensen — Wed, 22 Sep 2021 16:54:45 GMT

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.

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

dkcoop — Thu, 23 Sep 2021 01:19:03 GMT

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!

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

Kurt_Bremser — Thu, 23 Sep 2021 05:05:22 GMT

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.

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

dkcoop — Thu, 23 Sep 2021 15:37:54 GMT

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?

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

Kurt_Bremser — Thu, 23 Sep 2021 16:56:32 GMT

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