Loop between dates

Question

I created below macro to generate few datasets based on date macro.

%macro pull(date);

proc sql;

create table new&date as 

select * from acct

where date=&date.;

quit;

%mend;

So if i want to create dataset for 20170101 20170201 20170301 20170401 20170501, all i can do is use below macro

%macro pull(20170101)

%macro pull(20170201)

%macro pull(20170301)

%macro pull(20170401)

%macro pull(20170501)

What i am planning now is create two macro variables

%let begin=20170101;

%let end =20170501;

and create datasets based on begin and end using loop. Is it possible to do that.So what i am trying to do is give start and end date as macro variable and pull records between begin and end date from acct dataset and create separate datasets for each month between start and end dates

Note dataset have monthly dates for each year.

Below is the code i am trying

%let beg="01jan2000"d;
%let end="01jan2001"d; 
%macro Test;
%do date=&beg. %to &end.;
proc sql;
create table IPw_&date. as  
select *
from sample
where date=&date. quit;
%end;
%mend;
%Test;

What have you tried ? Do you know about interval functions such as INTNX ? Do you know about SAS date values and SAS date formats ? — Richard
Do you really need separate datasets? Why not just use where date between &date1 and &date2. — Tom
You may be interested in the SAS Macro Appendix which has a list of demo macros, one that shows how to loop through dates. See the list of sample code here, which includes a fully explanation. communities.sas.com/t5/SAS-Communities-Library/… — Reeza

Richard Richard · Accepted Answer · 2018-04-28T13:20:35

When date information must be inferred from values that are not SAS date values you will need to input the information to get a date value, and put the values iterated over to get the desired non date representation.

This example demonstrates

INPUTN function to parse the YYYYMMDD arguments into date values using informat YYMMDD8.
INTNX function to compute 1st of the month of the date values
PUTN function to convert a date value to a YYYYMMDD representation using format YYMMDDN8.
%DO %WHILE statement for iterating
INTNX function to advance the iteration variable to the start of the next month

Code

%macro pull(yyyymmdd);
  %local out;

  %let out = pull_&yyyymmdd;

  data &out;
    pull_date = input ("&yyyymmdd", yymmdd8.);
    format pull_date yymmdd10.;
  run;
%mend;

%macro pull_each_month(begin=, end=);
  %local 
    begin_date end_date
    begin_month end_month
    pull_date pull_ymd
  ;

  %put NOTE: &=begin &=end;

  %let begin_date = %sysfunc(inputn(&begin,yymmdd8.));
  %let end_date   = %sysfunc(inputn(&end,yymmdd8.));

  %put NOTE: &=begin_date &=end_date;

  %let begin_month = %sysfunc(intnx(month,&begin_date,0));
  %let end_month   = %sysfunc(intnx(month,&end_date,0));

  %put NOTE: &=begin_month &=end_month;

  %let pull_month = &begin_month;
  %do %while (&pull_month <= &end_month);
    %let pull_ymd = %sysfunc(putn(&pull_month,yymmddn8.));
    %put NOTE: Invoking pull for &=pull_month &=pull_ymd;

    %pull (&pull_ymd)

    %let pull_month = %sysfunc(INTNX(MONTH,&pull_month,1));

  %end;
%mend;

%pull_each_month (
  begin = 20170101
, end   = 20170501
)

%macro pull_each_month(begin=, end=);
  %local 
    begin_date end_date
    begin_month end_month
    pull_date pull_ymd
  ;

  %put NOTE: &=begin &=end;

  %let begin_date = %sysfunc(inputn(&begin,yymmdd8.));
  %let end_date   = %sysfunc(inputn(&end,yymmdd8.));

  %put NOTE: &=begin_date &=end_date;

  %let begin_month = %sysfunc(intnx(month,&begin_date,0));
  %let end_month   = %sysfunc(intnx(month,&end_date,0));

  %put NOTE: &=begin_month &=end_month;

  %let pull_month = &begin_month;
  %do %while (&pull_month <= &end_month);
    %let pull_ymd = %sysfunc(putn(&pull_month,yymmddn8.));
    %put NOTE: Invoking pull for &=pull_month &=pull_ymd;
    %let pull_month = %sysfunc(INTNX(MONTH,&pull_month,1));
  %end;

%mend;

%pull_each_month (
  begin = 20170101
, end   = 20170501
)

Loop between dates

2 Answers