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(¯o)
/*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
... View more
