SAS do loop with if statement

Question

I am trying to group by dataset in three month groups, or quarters, but as I'm starting from an arbitrary date, I cannot use the quarter function in sas.

Example data below of what I have and quarter is the column I need to create in SAS. The start date is always the same, so my initial quarter will be 3rd Sep 2018 - 3rd Dec 2018 and any active date falling in that quarter will be 1, then quarter 2 will be 3rd Dec 2018 - 3rd Mar 2019 and so on. This cannot be coded manually as the start date will change depending on the data, and the number of quarters could be up to 20+.

The code I have attempted so far is below

data test_Data_op;
set test_data end=eof;
%let j = 0;
%let start_date = start_Date;
if &start_Date. <= effective_dt < (&start_date. + 90) then quarter = &j.+1;
run;

This works and gives the first quarter correctly, but I can't figure out how to loop this for every following quarter? Any help will be greatly appreciated!

Answer 1

Based on the comment by @Lee. Edited to match the data from the screenshot.

The example shows that May 11 would be in the 3rd quarter since the seed date is September 3.

data have;
input mydate :yymmdd10.;
format mydate yymmddd10.;
datalines;
2018-09-13
2018-12-12
2019-05-11
;
run;

%let start_date='03sep2018'd;

data want;
set have;
quarter=floor(mod((yrdif(&start_date,mydate)*4),4))+1;
run;

If you want the number of quarters to extend beyond 4 (e.g. September 4, 2019 would be in quarter 5 rather than cycle back to 1), then remove the "mod" from the function:

quarter=floor(yrdif(&start_date,mydate)*4)+1;

Answer 2

The traditional use of quarter means a 3 month time period relative to Jan 1. Make sure your audience understands the phrase quarter in your data presentation actually means 3 months relative to some arbitrary starting point.

The funky quarter can be functionally computed from a months apart derived using a mix of INTCK for the baseline months computation and a logical expression for adjusting with relation to the day of the month of the start date. No loops required.

For example:

data have;
  do startDate = '11feb2019'd ;
    do effectiveDate = startDate to startDate + 21*90;
      output;
    end;
  end;
  format startDate effectiveDate yymmdd10.;
run;

data want;
  set have;

  qtr = 1 
      + floor(
          ( intck ('month', startDate, effectiveDate) 
            -
            (day(effectiveDate) < day(startDate))
          ) 
          / 3
        );

  format qtr 4.;
run;

Extra

Comparing my method (qtr) to @Tom (qtr_number) for a range of startDates:

data have;
  retain seq 0;
  do startDate = '01jan1999'd to '15jan2001'd;
    seq + 1;
    do effectiveDate = startDate to startDate + 21*90;
      output;
    end;
  end;
  format startDate effectiveDate yymmdd10.;
run;

data want;
  set have;

  qtr = 1 
      + floor( ( intck ('month', startDate, effectiveDate) 
                 - (day(effectiveDate) < day(startDate))
               ) / 3 );

  month_number=intck('month',startDate,effectiveDate,'cont')+1;
  qtr_number=floor((month_number-1)/3)+1;

  format qtr: month: 4.;
run;

options nocenter nodate nonumber;title;
ods listing;

proc print data=want;
  where qtr ne qtr_number;
run;

dm 'output';

-------- OUTPUT ---------

                                effective            month_     qtr_
    Obs    seq     startDate          Date     qtr    number    number

  56820     31    1999-01-31    1999-04-30       1        4         2
  57186     31    1999-01-31    2000-04-30       5       16         6
  57551     31    1999-01-31    2001-04-30       9       28        10
  57916     31    1999-01-31    2002-04-30      13       40        14
  58281     31    1999-01-31    2003-04-30      17       52        18
 168391     90    1999-03-31    1999-06-30       1        4         2
 168483     90    1999-03-31    1999-09-30       2        7         3
 168757     90    1999-03-31    2000-06-30       5       16         6
 168849     90    1999-03-31    2000-09-30       6       19         7
 169122     90    1999-03-31    2001-06-30       9       28        10
 169214     90    1999-03-31    2001-09-30      10       31        11
 169487     90    1999-03-31    2002-06-30      13       40        14
 169579     90    1999-03-31    2002-09-30      14       43        15
 169852     90    1999-03-31    2003-06-30      17       52        18
 169944     90    1999-03-31    2003-09-30      18       55        19
 280510    149    1999-05-29    2001-02-28       7       22         8
 280875    149    1999-05-29    2002-02-28      11       34        12
 281240    149    1999-05-29    2003-02-28      15       46        16
 282035    150    1999-05-30    2000-02-29       3       10         4
 282400    150    1999-05-30    2001-02-28       7       22         8
 282765    150    1999-05-30    2002-02-28      11       34        12

Answer 3

No need for a DO loop if you already have the start_date and actual event dates. Just count the number of months and divide by three. Use the continuous method of the INTCK() function to handle start dates that are not the first day of a month.

month_number=intck('month',&start_date,mydate,'cont')+1;
qtr_number=floor((month_number-1)/3)+1;