How to form a loop of 12 month date values with change in every quarterly Date values in SAS DI

[Neha8]

 I am new to SAS DI. I have one column with monthly date values (DATE) and one column with corresponding quarter values (Date_quarter). 

I need a table wherein for every quarter date values, I get last 12 month values iteratively. Now I am not sure, how to do it SAS DI.

what I have done is in SAS Enterprise Guide, created a macro variable, vdatum, which takes every quarter end values.

%let vdatum1 = '30.03.2016'd;

%let vdatum2 = '30.06.2016'd;

%let vdatum3 = '29.09.2016'd;

%let vdatum4 = '30.12.2016'd;

%let vdatum5 = '30.03.2017'd;

%letvdatum6 = '30.06.2017'd;

%let vdatum7 = '29.09.2017'd;

%let vdatum8 = '30.12.2017'd;

%macro loop(iteration_number);

   %do i= 1 %to &iteration_number;

      data loop_trial;

      set trial.data;

      where date > intnx('month',&&vdatum&i.,-12,'sameday') and date <= &&vdatum&i.;

      Date_quarter = intnx('qtr',&&vdatum&i.,0,'beginning');

      run;

  %end;

%mend;

%loop(8);

[Tom]

The first obvious problem is that date literals have to be in a form that the DATE informat can read.

Is there a pattern to those dates?  Some appear the be the last day of a quarter, like June 30th.  But other, like March 30th, are the next to last day of the quarter.

Why are you using INTNX() to move by 12 months instead of 1 year?

What does this sentence mean?

I need a table wherein for every quarter date values, I get last 12 month values iteratively.

What  is the over all goal of this exercise?  Are you trying to replicate your data?  Collapse it into intervals? (isn't that what the DATE_QUARTER variable already does?)  Summarize it some how?

[VDD]

are you looking for engagement into an event during the last 12 months?  basically creating a window period of time?

[ScottBass]

I started this, but realized I was on the wrong track:

%let start = 30MAR2016;
%let num   = 8;

* dummy format for debugging ;
proc format;
   value dates2qtrnum
      1 -< 2 = 3
   ;
run;

* view the structure of the cntlout table ;
proc format cntlout=cntlout;
run;

* create desired cntlin table ;
data cntlin;
   length fmtname $32 start end label 8 type eexcl $1;
   fmtname="DATES2QTRGRP";
   type="N";
   eexcl="Y";
   start="&start"d;
   do label=1 to &num;
      end=intnx('qtr',"&start"d,label,'B');
      output;
      start=end;
   end;
   format start end date9.;
run;

But the concept is to:

• Create a range format to group your data into the correct buckets.
• Apply the format to your dates to derive the correct bucket.
• Use BY processing to split your data

As far as how to do this in DI, once you get the correct code running in EG, just use a user-written transformation and post your working code into DI.