topic Re: DO loop with date in SAS Programming

DO loop with date

grace999 — Wed, 16 Jan 2019 06:27:44 GMT

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;

Re: DO loop with date

Kurt_Bremser — Mon, 14 Jan 2019 08:41:33 GMT

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

Re: DO loop with date

grace999 — Mon, 14 Jan 2019 11:31:56 GMT

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

Re: DO loop with date

PeterClemmensen — Mon, 14 Jan 2019 12:09:19 GMT

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;

Re: DO loop with date

Kurt_Bremser — Mon, 14 Jan 2019 12:09:26 GMT

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;

Re: DO loop with date

grace999 — Wed, 16 Jan 2019 09:45:37 GMT

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;