BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Reeza
Super User

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

sk423
Obsidian | Level 7
Looking at my macro structure, do you have any suggestion?
Reeza
Super User

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

 

 

sk423
Obsidian | Level 7
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
Super User

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
Obsidian | Level 7
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
Super User Tom
Super User

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;

image.png

Tom
Super User Tom
Super User

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
Obsidian | Level 7

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!

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 23 replies
  • 1481 views
  • 6 likes
  • 5 in conversation