Extracting Year Month and Quarters between two dates

[tanya_henderson]

I found this post about extracting quarters from a date: 

https://communities.sas.com/t5/SAS-Data-Mining-and-Machine/Extracting-Month-Year-and-quarter/m-p/213...

I would like to do something similar but I want to extract all of the quarters that have happened since a specific date. 

So if I enter a date of 12SEP2016 I would get something like this:

Y2016Q3 Y2016Q4 Y2017Q1 Y2017Q2 Y2017Q3 Y2017Q4 Y2018Q1 Y2018Q2 Y2018Q3 Y2018Q4 Y2019Q1 Y2019Q2

I am fine if I need to get this in pieces and concatenate them, I am just trying to determine how to get these dates without running through every single day from start date to today. 

My goal for this is to help me with proc report and adding columns for the quarter as new quarters become available. 

[Reeza]

I highly highly recommend that you don't do this. Instead, create the date as a single column and use as an ACROSS variable within PROC REPORT. Or Transpose after if needed, which would create those columns automatically and you don't have to. If you really, really need to for some reason, you can use INTCK() to determine the number of intervals between the dates and create the names.

Untested example below on how to create this series:

data demo;

date = '01Jan2018'd;

nQuarters = intck('qtr', date, today());

do i=1 to nQuarters;
date = intnx('qtr', date, i);
output;
end;

run;

---

@tanya_henderson wrote:

I found this post about extracting quarters from a date: 

https://communities.sas.com/t5/SAS-Data-Mining-and-Machine/Extracting-Month-Year-and-quarter/m-p/213...

I would like to do something similar but I want to extract all of the quarters that have happened since a specific date. 

So if I enter a date of 12SEP2016 I would get something like this:

Y2016Q3 Y2016Q4 Y2017Q1 Y2017Q2 Y2017Q3 Y2017Q4 Y2018Q1 Y2018Q2 Y2018Q3 Y2018Q4 Y2019Q1 Y2019Q2

I am fine if I need to get this in pieces and concatenate them, I am just trying to determine how to get these dates without running through every single day from start date to today. 

My goal for this is to help me with proc report and adding columns for the quarter as new quarters become available. 

---

[tanya_henderson]

The results of this process were not accurate. I have had trouble with using the intnx before as well. 

The corresponding data returns:

                  date          nQuarters        i

	01APR2018	5	1
	01OCT2018	5	2
	01JUL2019	5	3
	01JUL2020	5	4
	01OCT2021	5	5

What I would hope for was 01JAN2018, 01APR2018, 01JUL2018, 01OCT2018, 01JAN2019, 01APR2019

so while there had been 5 additional quarters, the dates were inaccurate. 

[Reeza]

Play around with the INTNX options to get the date set to what you want. You didn't provide dates so I had nothing to check against, but you should be able to get what you need by modifying the parameters within the INTNX function (the third and fourth parameters). You can also apply a format so it looks the way you want, so not sure why the date would matter if the quarter shows correctly.

[tanya_henderson · Accepted solution]

Here is how I solved the issue, I queried the results of the macro below. It does cycle through each date for now, but it gives me the result I needed. 

%let ds_list=;
proc sql;
select distinct 'Y'||rpt_qtr into :ds_list separated by ' '
from study_calendar
;
quit;
%put &=ds_list;

%macro get_study_calendar;
%let macro= get_study_calendar;
  %in_mac(&macro)
    
  /*Get First Screening and Protocol begin and end dates */
%get_metric_dates

 data dates; 
        format consntdt lastdate date9.;
        consntdt = &m_frstscr;  /* This could be read in instead of being created in get_metric_Dates macro */
        lastdate = today(); 
        weeks = ceil((lastdate-consntdt)/7);
        drop c_consntdt;
        run; 

/* this calculates max weeks and stores in macro var */;
proc means data=dates;
var weeks;
output out=maxweeks max=maxweeks;
run;
data _null_;
set maxweeks;
call symputx("weeks",maxweeks);
run;

data weeks;
set dates;
array week_array [*] week1-week&weeks;
do i = 1 to &weeks;
nm = i;
week_array[i] = consntdt + (7*(i));
end;
run;  
	
proc transpose data = weeks out = weeksx; 
run; 

data weeksxB; 
set weeksx; 
format weekend weekstart date9.;
weeknm = _NAME_; 
weekend = COL1;
if _name_ = 'week1' then weekstart = COL1 -7; 
else weekstart = COL1-6;
if weekend> today()+7 then delete; 
if weeknm in ('consntdt', 'lastdate', 'weeks', 'i', 'nm') then delete;
run; 

data calendar_dates;
  length key 8; 
  do date = &m_frstscr to &m_currdt; 
    key + 1; 
    RSA_WorkdayInd = ( 2 <= weekday(date) <= 6);
    WeekendInd = (not RSA_WorkdayInd);
    Calendar_Week_Number = week(date, "V");
    DayOfWeek = put(date, downame3.);
    Fin_Year = year(date);
	Fin_Month = month(date);
	FIN_qtr = QTR(date); 
    output; 
  end; 

  format 
    date date9.
  ;
run; 

proc sql; 
create table qtr_bound as 
select  min(date) as qtr_beg format =date9., max(date) as qtr_end format =date9., fin_year, fin_qtr from calendar_dates
group by fin_year, fin_qtr; 
quit; 

proc sort data = qtr_bound; 
by fin_year fin_qtr; 
run; 
proc sort data = calendar_dates; 
by fin_year fin_qtr date; 
run; 

data calendar_dates; 
merge calendar_dates qtr_bound; 
by fin_year fin_qtr; 
run; 

proc sql; 
create table cal_dates as 
select key, date, fin_year, fin_month, fin_qtr, qtr_beg, qtr_end, weekend, weekstart, weeknm, Calendar_Week_Number from calendar_dates c inner join weeksxB  B on  date between weekend and weekstart; 
quit; 

data study_calendar; 
set cal_dates; 
rpt_wk = cats(fin_year , "W",Calendar_Week_Number);
rpt_month = cats(fin_year , "M", Fin_Month);
rpt_qtr = cats(fin_year ,"Q", fin_qtr);

keep date rpt_wk rpt_month rpt_qtr qtr_beg  qtr_end weekend weekstart; 
   run; 
%mend;
%get_study_calendar