topic Re: Quarterly Date Macro in SAS Enterprise Guide

Quarterly Date Macro

claforest1 — Tue, 29 Oct 2019 13:56:20 GMT

Hi I am trying to create a macro date variable for quarterly datasets. I am pulling data from 9+ years of datasets at quarterly intervals and the datasets have the name format of: loandata_YYYYMM. For example, I need a line of data from loandata_201801, loandata_201804, loandata_201807 and loandata_201810. I know how to write the macro manually (see code below), but I also know there is definitely a quicker way to write this. Each year we run this code, we will need the new year's quarterly data. The code below is the SUPER abbreviated version of what I have, it's missing the proc sql create table section and a few calculated variables. I just wanted to show the macro.

Thank you!

%macro extract_portfolio(date_input, month);

data ext_&date_input.&month.;
	set loandata_&date_input.&month. (keep= yada yada yada);


%mend;
%extract_portfolio(2010, 10);
%extract_portfolio(2011, 01);
%extract_portfolio(2011, 04);
%extract_portfolio(2011, 07);
%extract_portfolio(2011, 10);
%extract_portfolio(2012, 01);
%extract_portfolio(2012, 04);
%extract_portfolio(2012, 07);
%extract_portfolio(2012, 10);
%extract_portfolio(2013, 01);
%extract_portfolio(2013, 04);


/* Add new year */

data home.portfolio_;
	set	home.portfolio_201010
		home.portfolio_201101
		home.portfolio_201104
		home.portfolio_201107
		home.portfolio_201110
		home.portfolio_201201
		home.portfolio_201204
		home.portfolio_201207
		home.portfolio_201210
                home.portfolio_201301
                home.portfolio_201304
;
run;

Re: Quarterly Date Macro

Astounding — Tue, 29 Oct 2019 14:59:11 GMT

For now, you should forget about how to write this faster. You should concentrate on how to design it better, so it will run in half the time. Your design should aim for something like:

data home.portfolio_;
	set	loandata_201010 (keep=yada yada yada)
                loandata_201101 (keep=yada yada yada)
                loandata_201104 (keep=yada yada yada)
                .....
                ;
run;

Extract the data one time instead of twice. Once you get it to work without macros, then worry about adding macros. If you apply macros too soon, you will embed the inefficiencies forever.

Re: Quarterly Date Macro

Tom — Tue, 29 Oct 2019 15:14:14 GMT

Part of your problem will be how to generate the series of suffixes for the dataset names: 201010,201101,201104,....

How do want to control what series to generate? Do you want to start from a given month and specify a number of files? Do you want to specify start and end dates? Do you want to specify the dates as real dates? Or as the YYYYMM string that is used in the dataset names? or in the Year,Month combo like your posted example?

Here is how you might do it when user specifies a start date plus a count of the number of quarters.

%macro mymacro(start,count);
%local i yymm;
%do i=1 %to &count;
   %let yymm=%sysfunc(intnx(qtr,&start,&i-1,b),yymmn6.);
   ... code that uses &YYMM as part of dataset name ....
%end;
%mend mymacro;

%mymacro('01oct2010'd,11);

Results:

65    %macro mymacro(start,count);
66    %local i yymm;
67    %do i=1 %to &count;
68       %let yymm=%sysfunc(intnx(qtr,&start,&i-1,b),yymmn6.);
69       %put &=start &=i &=yymm ;
70    %end;
71    %mend mymacro;
72
73    %mymacro('01oct2010'd,11);
START='01oct2010'd I=1 YYMM=201010
START='01oct2010'd I=2 YYMM=201101
START='01oct2010'd I=3 YYMM=201104
START='01oct2010'd I=4 YYMM=201107
START='01oct2010'd I=5 YYMM=201110
START='01oct2010'd I=6 YYMM=201201
START='01oct2010'd I=7 YYMM=201204
START='01oct2010'd I=8 YYMM=201207
START='01oct2010'd I=9 YYMM=201210
START='01oct2010'd I=10 YYMM=201301
START='01oct2010'd I=11 YYMM=201304

Re: Quarterly Date Macro

Patrick — Wed, 30 Oct 2019 22:51:06 GMT

Here how you can generate the list of desired table names.

/* create sample data */
%macro createSampleData();
  %do mm=1 %to 12;
    %let mm=%sysfunc(putn(&mm.,z2.));
    %do yyyy=2009 %to 2018;
      data loandata_&yyyy.&mm.;
        set sashelp.class(obs=3);
      run;
    %end;
  %end;
%mend;
%createSampleData();

/* create macro variable &dslist with list of table names beginning of quarter */
proc sql noprint;
  select catx('.',libname,memname) into :dslist separated by ' '
  from dictionary.tables
  where 
    libname='WORK' 
    and memname like 'LOANDATA^_%' escape '^'
    and input(scan(memname,2,'_'),yymmn6.) = intnx('quarter',input(scan(memname,2,'_'),yymmn6.),0,'b')
  ;
quit;
%put &=dslist;

/* create table want */
data want;
  length _sourceDS sourceDS $41;
  set &dslist indsname=_sourceDS;
  sourceDS=_sourceDS;
run;

proc print data=want;
run;