Macros for concurrent months

[meckarthik]

Hi All,

I need help on macro for getting concurrent weeks. Below an example for 5 customers in campaigns where the start and end dates are different. I need a macro to state which week they fall into. As you can customer A started to campaign on 01Jan -19 and finished on 05 March so I need to count them in all weeks until they reach the end date 

Customer	Start date	End Date	wk1_2019	wk2_2019	wk3_2019	……	Wk9_2019	wk10_2019	wk11_2019	wk12_2019	wk13_2019	wk14_2019	wk15_2019
A	01/01/2019	05/03/2019	1	1	1	1	1	0	0	0	0	0	0
B	05/01/2019	07/03/2019	1	1	1	1	1	1
C	01/03/2019	10/03/2019	1	1	1	1	1	1
D	05/03/2019	25/03/2019					1	1	1
E	20/03/2019	10/04/2019							1	1	1

[Kurt_Bremser]

No need for a macro. All this can be done in a data step:

data want;
set have;
do date = start_date to end_date by 7;
  year = year(date)
  week = week(date);
  output;
end;
keep customer year week;
run;

With this, you can easily get counts of customers per year and week.

[meckarthik]

Thank you .. but i'm getting below error, i'm in learning stage!!

25 GOPTIONS ACCESSIBLE;
26 data test;
27 set Campaign_extract;
28 do date = start_date to end_date by 7;
29 year = year(StartDate);
30 week = week(StartDate);
31 output;
32 end;
33 keep LMCampNo year week;
34 run;

NOTE: Variable start_date is uninitialized.
NOTE: Variable end_date is uninitialized.
ERROR: Invalid DO loop control information, either the INITIAL or TO expression is missing or the BY expression is missing, zero,
or invalid.
LMCampNo=12345 StartDate=26OCT2017 EndDate=30SEP2018 RevenueBudget=100  ClockNumber=ABD/123/567 IDSAdSmartableHouseHolds=0
Status=A LMContractedImpressions=100LMDeliveredImpressions=10ImpressionCap=0 DailyImpressionCap=17
Years=2017 date=. start_date=.
end_date=. week=. _ERROR_=1 _N_=1

[meckarthik]

Thank you .. but i'm getting below error, i'm in learning stage!!



25 GOPTIONS ACCESSIBLE;
26 data test;
27 set Campaign_extract;
28 do date = start_date to end_date by 7;
29 year = year(StartDate);
30 week = week(StartDate);
31 output;
32 end;
33 keep LMCampNo year week;
34 run;

NOTE: Variable start_date is uninitialized.
NOTE: Variable end_date is uninitialized.
ERROR: Invalid DO loop control information, either the INITIAL or TO
expression is missing or the BY expression is missing, zero,
or invalid.
LMCampNo=12345 StartDate=26OCT2017 EndDate=30SEP2018 RevenueBudget=100
ClockNumber=ABD/123/567 IDSAdSmartableHouseHolds=0
Status=A
LMContractedImpressions=100LMDeliveredImpressions=10ImpressionCap=0
DailyImpressionCap=17
Years=2017 date=. start_date=.
end_date=. week=. _ERROR_=1 _N_=1

[Kurt_Bremser · Accepted solution]

Compare my code to yours, especially the arguments to the year() and week() function.

Note that I had to make up the variable names, as you did not provide example data in usable form (data step with datalines). And the column names in your original post are clearly invalid, as they contain blanks.

It's up to you to insert the correct variable names as they are in your dataset.

[meckarthik]

Many thanks !