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 |
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.
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.
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
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.
Many thanks !
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.