DO loop with date

[grace999]

Need to find the prior month/week average sales in terms of volume. no sales on weekends or holidays.
My sample code is below, the where statement and the table name have some issues. Can someone help me? Thanks in advance!

%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.));
proc sql;
CREATE table s&day. AS
(select team, count(sales)/count(distinct date) as avg_sales
from have(where=(intnx('day', &day., -6)<=datepart(date)<=intnx('day', &day., -1))) 
group by 1 
;QUIT;
%end;
%prior_wk_n;

[Kurt_Bremser]

Please provide some example data (in a data step with datalines, see my footnotes), and what you expect out of it.

[grace999]

prior week data sample: 

team	date	sale_amount
a	1-Jan-19	$ 393
a	1-Jan-19	$ 178
a	2-Jan-19	$ 105
a	3-Jan-19	$  65
a	3-Jan-19	$  33
a	3-Jan-19	$  27
a	4-Jan-19	$  23
a	5-Jan-19	$  21
a	5-Jan-19	$  15
b	1-Jan-19	$  90
b	1-Jan-19	$ 171
b	2-Jan-19	$  98
b	2-Jan-19	$  20
b	3-Jan-19	$  26
b	4-Jan-19	$  16
b	5-Jan-19	$  14
b	5-Jan-19	$    8

The prior week summary for date Jan, 8th, 2019 should be:

team	date	prior_wk_daily_n_sales	prior_wk_daily_dollar_sales
a	8-Jan-19	1.8	$     172
b	8-Jan-19	1.6	$   88.60

[Kurt_Bremser]

If your analysis is grouped along calendar weeks, you can use the week() function to your advantage:

proc sql;
create table want as
select
  team,
  week(date) as week,
  count(distinct date) as count_days,
  count(*) as count_sales,
  sum(sale_amount) as sum_sales,
  calculated count_sales / calculated count_days as prior_wk_daily_n_sales,
  calculated sum_sales / calculated count_days as prior_wk_daily_dollar_sales,
  intnx('week',max(date),1,'b') + 2 as date format=yymmddd10.
from have
group by team, week;
quit;

[PeterClemmensen]

data have;
input team $ date:date9. sale_amount:comma8.;
datalines;
a 1-Jan-19  $393
a 1-Jan-19  $178
a 2-Jan-19  $105
a 3-Jan-19  $65
a 3-Jan-19  $33
a 3-Jan-19  $27
a 4-Jan-19  $23
a 5-Jan-19  $21
a 5-Jan-19  $15
b 1-Jan-19  $90
b 1-Jan-19  $171
b 2-Jan-19  $98
b 2-Jan-19  $20
b 3-Jan-19  $26
b 4-Jan-19  $16
b 5-Jan-19  $14
b 5-Jan-19  $8
;

proc sql;
   create table want as
   select team,
          today() as ReportDate format=date9.,
          count(sale_amount)/count(distinct date) as prior_wk_daily_n_sales,
          sum(sale_amount)/count(distinct date) as prior_wk_daily_dollar_sales format=dollar8.
   from have
   group by team;
quit;

[grace999]

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;