data want(where= (wf =0) keep=day c_total wf); set have; wf = mod(day,7); flag = wf ~=1; retain c_total 0; c_total = (c_total*flag + total); run; ... View more

Thanks everybody for the quick response you guys provided in this thread.   Special thanks to Tom and Reeza, thank you very much for all your suggestions and feedbacks.   I modified my macro (strucure giben in Message 13) to be self-contained by making "var_name" to be "var_list" table and adding an additional loop to go through all the variables.   Tom, you code sample is great and as a noob in SAS it is definitely a treasure for me (I was doing some stuffs that you did in your code so differently). It also generates what I need so I will accept it as a solution for this thread.   Best! ... View more

You have hit the nail there, that's exactly where I am looking for help. Either by modifying my macro to be self-contained or by create a data step outside that can loop over the variable list and appending the outputs while getting rid of temp tables. And, my groupby_var does not change, it was only for the identification of groupby variable. For now the only thing changing is the var_name which I want to pull from another table. ... View more

Yes I have lagged variables which I use to get the moving average. I wrote this from scratch, was trying to be dynamic. Long shot I am looking to do various operations on those windows. ... View more

Looking at my macro structure, do you have any suggestion? ... View more

I already have a macro that gives me the moving average, the structure shown above is of what I have.   I am trying to learn what's an efficient way of putting it together for scaling. To Reeza's point self-contained macro or data step like below (which is not correct). I want to be able to loop through the variables (open, high, low and close) and have a combined "final" data. want1 - want4 are intermediate outputs that I get.   I am looking for a SAS code structure that is efficient for work like this with large dataset.   data final; set have; %moving_avg(have, want1, stock, date, open, 4, '2,3,4'); %moving_avg(have, want2, stock, date, high, 4, '2,3,4'); %moving_avg(have, want3, stock, date, low, 4, '2,3,4'); %moving_avg(have, want4, stock, date, close, 4, '2,3,4'); run; Thanks for your response Tom! ... View more

  %macro moving_avg(have, want, groupby_var, time_var, var_name, max_window_size, window_sizes); /* Sort "have" by "groupby_var" and "time_var" */ /* Assign an array of macro with window size values (includes multiple steps) */ data want; set have; by groupby_var; /* Create array using "max_window_size" for lag variables */ /* Create lag variables (this creates additional lag columns: lag_1-lag_max_window_size) */ /* Here I have a step to correct for the bleeding of the lag variable to the next group */ /*I use a do loop to calculate mov_avg for various window size; "&abc" in the code below is to get the window size dynamically*/ %do j = 1 %to &yy.; &var_name.avg_&&abc&j = round(mean(of lag1 - lag&&abc&j), 0.01); %end; /* drop variables that are not required */ run; %mend;   Does this help for the macro content? ... View more

Reeza thanks for your links.   So, let's say I have a macro like this   %macro moving_avg(have, want, groupby_var, time_var, var_name, max_window_size, window_sizes); an execution example of which is      %moving_avg(have, want, stock, date, open, 7, '2,3,5,7'); "max_window_size" is used to create maximum number of lag needed to accomodate all window size.   "window_sizes" is all possible window size I want to do moving avg for. "have" has columns "stock", "date" and "open" "want" will have columns "stock", "date", "open", "avg2", "avg3", "avg5" and "avg7"   If I need to do this for all other columns that are numeric not just "open" how do I structure my data step?     ... View more

Thanks for your response ballardw! proc sort data=sashelp.stocks out=stocks; by stock date; run; data stocks; set stocks (where =(Date>'01SEP04'd)); drop high low close Volume AdjClose; run; I just made a smaller data for inital start. The output sample is only a snapshot of that. I want various length of moving average, 2 months, 3 months, 5 months, 7 months etc. (one column output for each). So 3_month_average in row with date AUG2017 should have average of (AUG2017, JUL2017, JUN2017). Similarly, 3_month_average in row with date JUL2017 should have average of (JUL2017, JUN2017, MAY2017), and so on.   I did a descending sort by date to show the final sample output hence in reverse order.   I have been doing this by doing a dynamic lag using a very complicated macro. I am looking for an efficient way so that I can scale it and apply the same to multiple attributes like "open".       ... View more

Thanks for your response Reeza, but unfortunately I do not have license for either of those. Trying to do this using data step. ... View more

Thanks for your quick response RW9 and sorry for not being clear.   Test data is sashelp.stocks data, so I assumed it would be accessible to a SAS user hence i used proc sort to show sample test data. I felt the first step will be to sort the data by "stock" and "date" so the code.   Screenshot is only for the sample expected output.   Let's forget about macro for now and say I want to calculate moving average for various window size (2, 3, 5, 7 etc.)     ... View more

The sample output is shown in the picture contained in original post. Macro use was referenced to calculation of average and similar operation on the given window size. Window is dynamic, as shown in sample output i want output column for each window size. ... View more