turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- How to loop using PROC EXPAND?

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-16-2010 04:26 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Sj03rd

07-17-2010 10:48 AM

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;

%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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Patrick

07-18-2010 11:15 PM

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!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Sj03rd

07-17-2010 10:51 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Cynthia_sas

07-18-2010 11:16 PM

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