Dear SAS community: I have a dataset like the following: Week geogkey volume 3/10/2019 unique_geogkey1 15000 "All dates in between" .... 12/15/2019 unique_geogkey2 16000 3/8/2020 unique_geogkey1 17000 "All dates in between" ... 12/13/2020 unique_geogkey1 18000 I want to perform a computation between the current year date and the corresponding previous year date starting from 3/8/2020 until 12/13/2020. In my dataset, there are 62 unique geogkeys, each of them mapped to all the unique weeks. So total observations = 62*156 weeks. I want to group this by each unique geogkey and week combination. i.e. there is a unique volume value for each week and geogkey combination. For example, (3/8/2020 - 3/10/2019)*2 = (17000-15633)*2 Do this for each subsequent date until 12/13/2020 and the corresponding previous year (12/15/2019) Is there a method that performs a more complex calculation besides the simple ones in proc summary e.g. sum= means= ? I am currently attempting with a macro code to no avail: start_date = '08Mar2020'd;
end_date = '13Dec2020'd;
%macro catyrind(start_date,end_date);
%do cat_period = &start_date %to &end_date;
%let prev_date=%sysfunc(intnx(year,cat_period,-1,"sameday"));
proc means data=walmart_catyrind nway noprint;
class week geogkey;
var sc; *this variable contains numeric values in the table
output out=final_wm ((cat_period)/(&prev_date))*100-100; *This is the necessary calculation
run;
%end;
%mend;
%catyrind(start_date,end_date); I know the calculation above is out of order but was testing to see if it can be performed within the proc summary step. *The final output for the week variable should ONLY include the corresponding 2020 date and the 2019 week date will be dropped. The output would look something like this: Geogkey Week calculation unique_id 3/8/2020 (volume of 3/8/2020) / (volume of 3/10/2019) *100-100 ..... unique_id unique_week_2020year All volume for weeks in between ...... unique_id 12/13/2020 (volume of 12/13/2020) / (volume of 12/15/2019) *100-100 Doesn't have to be a macro, any method would be helpful. Thanks in advance!
... View more
