window operation by group

[Reeza]

Then you need to redesign your macro to not work with a defined data set and output. 

[sk423]

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

[Reeza]

It seems to assume you have lagged variables, is that correct? Did you write this or 'find' it somewhere? 

[sk423]

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.

[Reeza]

So this recreates the data set each time. That's inefficient. You want code generated to work within a single data step so it's all one pass of hte data. 

But - you've also specified unique groupby as an option. What happens if you put in different groupbys for different variables? That won't work as expected. 

It has to work for what you need though, so at the end of the day those are your choices. 

[sk423]

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.

[Tom]

Concrete examples help. So let's take SASHELP.STOCKS, which has monthly data, and generate 3 and 4 month moving averages for the OPEN and CLOSE prices.

Is this what you are trying to be able to do?

proc sort data=sashelp.stocks out=stocks;
  where Date>'01SEP2004'd ;
  by stock date;
run;
data stocks;
  set stocks ;
  by stock date;
  format _numeric_ ;
  format date yymmdd10. ; 
  if intck('month',lag(date),date) ne 1 and not first.stock then 
    put 'ERROR: Missing month' (_n_ _all_) (=)
  ;
run;

data want ;
  set stocks(keep=stock date open close);
  by stock date ;
  n+1 ;
  array lag3_open (0:2) _temporary_ ;
  array lag4_open (0:3) _temporary_ ;
  array lag3_close (0:2) _temporary_ ;
  array lag4_close (0:3) _temporary_ ;
  if first.stock then do;
    n=1;
    call missing(of lag3_open(*) lag4_open(*) lag3_close(*) lag4_close(*));
  end;
  lag3_open(mod(n,3))=open ;
  lag4_open(mod(n,4))=open ;
  lag3_close(mod(n,3))=close ;
  lag4_close(mod(n,4))=close ;
  if n >= 3 then open_avg3 = round(mean(of lag3_open(*)),.01);
  if n >= 4 then open_avg4 = round(mean(of lag4_open(*)),.01);
  if n >= 3 then close_avg3 = round(mean(of lag3_close(*)),.01);
  if n >= 4 then close_avg4 = round(mean(of lag4_close(*)),.01);
run;

proc print; run;

[Tom · Accepted solution]

So if you like that method then here is a macro to implement it.

%macro mavg(in,out,id,order,vars,lags);
%local i j ni nj var lag ;
%let ni=%sysfunc(countw(&vars));
%let nj=%sysfunc(countw(&lags));
data &out ;
  set &in(keep=&id &order &vars);
  by &id &order ;
  n+1 ;
%do i=1 %to ∋
  %let var=%scan(&vars,&i);
  %do j=1 %to &nj;
    %let lag=%scan(&lags,&j);
  array lag&lag._&var. (0:%eval(&lag.-1)) _temporary_ ;
  %end;
%end;
  if first.%scan(&id,-1) then do;
    n=1;
    call missing(of 
%do i=1 %to ∋
  %do j=1 %to &nj;
    lag%scan(&lags,&j)_%scan(&vars,&i)(*)
  %end;
%end;
    );
  end;
%do i=1 %to ∋
  %let var=%scan(&vars,&i);
  %do j=1 %to &nj;
    %let lag=%scan(&lags,&j);
  lag&lag._&var.(mod(n,&lag))=&var ;
  if n >= &lag. then &var._avg&lag. = round(mean(of lag&lag._&var.(*)),.01);
  %end;
%end;
run;
%mend mavg ;

And here is how to call it to replicate the results from before.

%mavg
(in=stocks
,out=want
,id=stock
,order=date
,vars=open close
,lags=3 4
);

[sk423]

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!