Thanks for providing suggestions, but that's not what I am looking for. I try to grab summary table from prior week or month of the know dates. For example, the known date is from Jan. 7th to Jan 11th 2019, but I am trying to get summary data from prior week (Jan. 1st to Jan 5th 2019) for Jan 7th; prior week(Jan 2nd to Jan 6th) for Jan 8th etc. so I was trying to use the %do loop. Please referring to my code. I need help with the do loop function, where statement to grab data from prior week. %let begin_date='07jan2019'd; %let end_date='11jan2019'd; %macro prior_wk_n; %do day=%sysfunc(inputn(&begin_date,date9.)) %to %sysfunc(inputn(&end_date,date9.)); %let name=day(&day.); proc sql; CREATE table s&name. AS (select team, count(sale_amount)/count(distinct date) as avg_sales from have(where=(intnx('day', &day., -7)<=date<=intnx('day', &day., -1))) group by 1 ;QUIT; %end; %mend; %prior_wk_n;
... View more
