CODEWITHSUNDEEP
sas
anand anand
anand anand

Reputation: 21

Loop between dates

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;

Upvotes: 1

Views: 1044

Answers (2)

Shenglin Chen
Shenglin Chen

Reputation: 4554

%macro pull(begin,end);
%let i=0;
%let begin=%sysfunc(inputn(&begin,anydtdte9.));
%let end=%sysfunc(inputn(&end,anydtdte9.));
%do %until (&begin=&end);
    %let begin=%sysfunc(intnx(month,&begin,&i));
    %let date=%sysfunc(putn(&begin,yymmddn8.));
    proc sql;
    create table new&date as 
    select * from acct where date=&date.;
    quit;
    %let i=%eval(&i+1);
%end;
%mend;

%pull(20170101,20170501)

Upvotes: 0

Richard
Richard

Reputation: 27498

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
)

Upvotes: 1

Related Questions