If you have a number of monthly or quarterly reports that use the same data, here is an idea of how to create/manage/override/utilize extracts that are used repeatedly. Why pull the same data from your data warehouse if an earlier report for the month already did?
Use options dlcreatedir; so that folders are created for you.
In this macro, &path is whatever high level path you have to your data.
&LOB_Folder is 'line of business' and you might use some other metric...state, county, customer, country, whatever.
&period I set as Q1-Q4 and 01-12 to cover quarters and months.
I set this up so that extracts can be generated for paid, received or service months or quarters.
It sets the variables, creates the folders, and checks to see if the dataset already exists (unless &refresh is set to Y in which case it recreates it).
Then it checks to see if there is an extract program for the table selected. If there is, it runs it.
So, I came up with this macro:
%macro extract(dsn,psr,year,period,refresh);
/* if any of these are missing, exit */
%if not %symexist(dsn) %then %return;
%if not %symexist(psr) %then %return;
%if not %symexist(year) %then %return;
%if not %symexist(period) %then %return;
%if not %symexist(refresh) %then %let refresh=N;
%let dsn=%upcase(&dsn);
%let psr=%upcase(&psr);
%let refresh=%upcase(&refresh);
%if %length(&period)=1 %then %let period=0.
%if &psr=P %then %let psr=Paid;
%if &psr=S %then %let psr=Service;
%if &psr=R %then %let psr=Received;
/* calc start and end dates */
data _null_;
format startdate date9. enddate date9.;
%if %substr(&period,1,1)=Q %then
%do;
startdate = INTNX( 'QTR', "01Jan&year"d, %substr(&period,2,1)-1, 'S' );
enddate = INTNX( 'QTR', "01Jan&year"d, %substr(&period,2,1)-1, 'E' );
%end;
%else
%do;
startdate = INTNX( 'MONTH', "01Jan&year"d, &period -1, 'S' );
enddate = INTNX( 'MONTH', "01Jan&year"d, &period -1, 'E' );
%end;
call symput('startdate',startdate);
call symput('enddate',enddate);
run;
%put &=startdate &=enddate;
%let startdate = %Sysfunc( PutN( &startdate , Date9 ) );
%let enddate = %Sysfunc( PutN( &enddate , Date9 ) );
%put &=startdate &=enddate;
%let Facets_Start_Date = %str(%')&startdate%str(%');
%let Facets_End_Date = %str(%')&enddate%str(%');
%put &=Facets_Start_Date &=Facets_End_Date;
/* create extract folder(s) if necessary */
libname PSR "&path./&LOB_FOLDER.";
libname PSR "&path./&LOB_FOLDER./_Extracts";
libname PSR "&path./&LOB_FOLDER./_Extracts/&Year.";
libname PSR "&path./&LOB_FOLDER./_Extracts/&Year./&period.";
libname PSR "&path./&LOB_FOLDER./_Extracts/&Year./&period./&psr.";
%let fqdsn=psr.&dsn;
/* if the dataset exists and we're not refreshing it, show 25 records */
%if %sysfunc(exist(&fqdsn)) and &refresh=N %then
%do;
title "&dsn exists in %sysfunc(pathname(psr)). Sample 25 records:";
proc sql outobs=25;
select * from &fqdsn;
quit;
title;
%end;
/* otherwise check to see if there is an extract program for the table. If there is, run it */
%else
%do;
%if %sysfunc(fileexist("&path./Common/Extracts/&dsn..sas"))=0 %then
%return;
%include "&path./Common/Extracts/&dsn..sas";
%end;
%mend extract;
So, a call to this extract macro might be something like this:
%include "&path/Common/Macros/Extract.sas";
%put &=Paid_Dates_min;
%let xxx = %Sysfunc( InputN( &Paid_Dates_min , date9 ) ) ;
%let DateCCYY = %Sysfunc( PutN( &xxx , year4 ) ) ;
%let Date_MM = %Sysfunc( PutN( &xxx , month2 ) ) ;
%put &=xxx &=startdate &=dateccyy &=date_mm;
%extract(claims,p,&DateCCYY,&Date_MM,n);
proc sql; create view sds.claims as select * from psr.claims; quit;
It adds the macro, calcs the year and month from the minimum paid date, checks to see if the extract for that year and month already exists. If it doesn't it creates it. Then, it creates a view for in the current work library (I use 'sds' for 'staging datasets') from the existing extract (libname psr for paid/service/received).
So, this allows you to save space and time by only doing an extract when it is needed and always being able to refresh it as well.
Examples of running it:
%extract(claims,p,2018,01,y); this creates/recreates the Jan 2018 pull for paid claims.
%extract(claims,p,2018,4,y); this does the same for Apr 2018. 4 and 04 are both okay.
%extract(claims,p,2018,6,n); Gen Jun 2018 pull if it doesn't exist.
%extract(claims,p,2018,3); it'll default to not refresh. Missing = N
%extract(claims,p,2017,2,b); same here. Anything other than Y = N
%extract(claims,s,2018,01,y); Find claims for services in Jan 2018.
%extract(claims,r,2018,Q2,n); Find all claims received in Q2 2018.
