topic Re: How to loop using PROC EXPAND? in SAS Procedures

How to loop using PROC EXPAND?

Sj03rd — Fri, 16 Jul 2010 20:26:22 GMT

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

Re: How to loop using PROC EXPAND?

Patrick — Sat, 17 Jul 2010 14:48:29 GMT

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;

Re: How to loop using PROC EXPAND?

Cynthia_sas — Sat, 17 Jul 2010 14:51:42 GMT

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

should help you find even more resources.

cynthia

Re: How to loop using PROC EXPAND?

Sj03rd — Mon, 19 Jul 2010 03:15:37 GMT

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!

Re: How to loop using PROC EXPAND?

Sj03rd — Mon, 19 Jul 2010 03:16:34 GMT

Thanks for the article Cynthia, I'll have a look at it.