topic Re: Repeating a sequence of dates in SAS Programming

Repeating a sequence of dates

stevenyan0127 — Fri, 08 Apr 2022 23:15:03 GMT

Hi, The current code generates 10 observations and 10 dates. I would like to repeat this sequence of dates 10 times so that there will 100 observations and the dates should be in that order during the repetition. for example obs 1-10, 11-20, 21-30, etc will all have the same set of dates. Any help would be appreciated. Thanks!

%let start_date=01Jan2008;
%let end_date=10Jan2008;

data Dates;
date="&start_date"d;
do while (date<="&end_date"d);
    output;
    date=intnx('day', date, 1, 's');
end;
format date date9.;
run;

Re: Repeating a sequence of dates

HarrySnart — Mon, 11 Apr 2022 09:46:01 GMT

Hi @stevenyan0127 , is this what you're after?

%macro dateGenerator(start,end,multi,out);

*define date range;
%let start_date=&start;
%let end_date=&end;

*generate list of dates;
data Dates;
date="&start_date"d;
do while (date<="&end_date"d);
    output;
    date=intnx('day', date, 1, 's');
end;
format date date9.;
run;

*initialise base table;
data dates_out;
set dates;
run;

*Set counter from desired multiplier;
%let counter=%SYSEVALF(&multi - 1);

*loop through multiplier adding dates table;
%do i=1 %to &counter;
data dates_out;
set dates_out dates;
run;
%end;

*create output table with rownumber;
data &out;
set dates_out;
rownum+1;
run;

%mend;

%dateGenerator(01Jan2008,10Jan2008,10,myDates);

I've used your original code and effectively put it into a loop which multiplies the number of records.

Re: Repeating a sequence of dates

PaigeMiller — Mon, 11 Apr 2022 10:03:33 GMT

%let start_date=01Jan2008;
%let end_date=10Jan2008;

data Dates;
date="&start_date"d;
count=0;
do while (date<="&end_date"d);
    count=count+1;
    do i=1 to 10;
         n=(i-1)*10+count;   
         output;
    end;
    date=date+1;
end;
format date date9.;
drop count i;
run;
proc sort data=dates;
    by n;
run;

Re: Repeating a sequence of dates

Kurt_Bremser — Mon, 11 Apr 2022 16:12:31 GMT

Nested DO loop:

%let start_date=01Jan2008;
%let end_date=10Jan2008;

data Dates;
do i = 1 to 10;
  do date = "&start_date."d to "&end_date."d;
    output;
  end;
end;
format date date9.;
drop i;
run;

Since dates are counts of days, it is not necessary to use the INTNX function to increment daily. INTNX is mainly needed for larger, irregular intervals like months, quarters or years.

Re: Repeating a sequence of dates

kris8 — Mon, 28 Nov 2022 16:39:09 GMT

Hi can you do this to repeat a year?

Re: Repeating a sequence of dates

kris8 — Mon, 28 Nov 2022 16:53:26 GMT

how would you do it for a year?

Re: Repeating a sequence of dates

Kurt_Bremser — Mon, 28 Nov 2022 22:50:09 GMT

Just set the start and end dates accordingly, and the loop will work through all 365 (or 366 in a leap year) days of the year.

Re: Repeating a sequence of dates

Tom — Mon, 28 Nov 2022 23:52:39 GMT

Why use a DO WHILE() when you are just iterating? Why not just a normal old regular DO loop?

Add another loop to generate the 10 groups.

data Dates;
  do group=1 to 10;
    do date="&start_date"d to "&end_date"d ;
      output;
    end;
  end;
  format date date9.;
run;