BookmarkSubscribeRSS Feed
Sj03rd
Calcite | Level 5
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
4 REPLIES 4
Patrick
Opal | Level 21
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;
Sj03rd
Calcite | Level 5
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!
Cynthia_sas
SAS Super FREQ
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
Sj03rd
Calcite | Level 5
Thanks for the article Cynthia, I'll have a look at it.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1539 views
  • 0 likes
  • 3 in conversation