New Contributor
Posts: 4

# How to loop using PROC EXPAND?

Hello,

Using PROC EXPAND, I would like to calculate moving averages for different variables.

Specifically, consider some variables (say x, y, and z) for which I would like to calculate some backward moving averages (20 observations) with different names (say mx, my, mz) which require 3 different minimum number of observations (let's say 20, 18 or 16). This COULD be done as follows:

proc expand data = indta out=outdta method=none;
by ID;
convert x = mx20 / transformout=(movave 20 trim 20);
convert y = my20 / transformout=(movave 20 trim 20);
convert z = mz20 / transformout=(movave 20 trim 20);
convert x = mx18 / transformout=(movave 20 trim 18);
convert y = my18 / transformout=(movave 20 trim 18);
convert z = mz18 / transformout=(movave 20 trim 18);
convert x = mx16 / transformout=(movave 20 trim 16);
convert y = my16 / transformout=(movave 20 trim 16);
convert z = mz16 / transformout=(movave 20 trim 16);
run;

This doesn't sound as a difficult question, but am struggling to create a more flexible solution. I hope someones knows how to automate this, for instance with the following input arguments:

%let indta = indata;
%let outdta = outdata;
%let estdays = 20;
%let mindays = 20 18 16;
%let VarIn = x y z;
%let VarOut = mx my mz;

thanks
Sjoerd

Message was edited by: Sj03rd Message was edited by: Sj03rd
Posts: 4,736

## Re: How to loop using PROC EXPAND?

Did you have something like the following in mind?

%let indta = indata;
%let outdta = outdata;
%let estdays = 20;
%let mindays = 20 18 16;
%let VarIn = x y z;
%let VarOut = mx my mz;

%macro test;
%let ind_1=1;
%do %while (%scan(&mindays,&ind_1) ne );
%let ind_2=1;
%do %while (%scan(&VarIn,&ind_2) ne );
convert %scan(&VarIn,&ind_2) = %scan(&VarOut,&ind_2)20 / transformout=(movave &estdays trim %scan(&mindays,&ind_1))%str(
%let ind_2=%eval(&ind_2 +1);
%end;
%let ind_1=%eval(&ind_1 +1);
%end;
%mend;

proc expand data = &indta out=&outdta method=none;
by ID;
%test
run;
New Contributor
Posts: 4

## Re: How to loop using PROC EXPAND?

Great Patrick, that's exactly what I was looking for. I did play around with some of the commands you use, but didn't know about the need for some others. Also, the structure of your code is very insightful. Thanks!
SAS Super FREQ
Posts: 9,371

## Re: How to loop using PROC EXPAND?

Hi:
It looks like you've started to learn a bit about the SAS Macro Facility. It would, indeed, allow you to make your program more flexible and "reusable"... as Patrick's example shows.

The best introduction to the SAS Macro Facility is this paper:
http://www2.sas.com/proceedings/sugi28/056-28.pdf

And there are many, many other useful postings on the SAS Macro Facility in this forum and in the SAS Macro documentation examples. Searching the foum and the documentation and also searching for user group papers on the topic of SAS Macro processing
search string:
SAS Macro Tutorial beginner

cynthia
New Contributor
Posts: 4