DATA Step, Macro, Functions and more

Repeat a section of code (multiple steps) for n iterations

Accepted Solution Solved
Reply
Occasional Contributor jo1
Occasional Contributor
Posts: 14
Accepted Solution

Repeat a section of code (multiple steps) for n iterations

I've got 4 steps of code that I'd like to repeat 100 times, appending the results each time to a data set (last procedure in the block of code).  I think I should be either using a Do Loop or Macro to achieve but I can't work out the correct syntax to make it happen.  The code is below.  Basically I want something that will treat this as a block and repeat it n times. Some direction would be appreciated.

 

data Sim_WD;

set Sim_WD;

Sim_value=COL1 + rand("Normal", 0, _RMSE_);

run;

 

proc sql;

create table MD_append as

select Site as Site,

Year as Year,

Season as Season,

Type as Type,

RegYear as RegYear,

max(Sim_value) as MD

from Sim_WD

group by Site, Year, Season, Type, RegYear;

run;

 

proc transpose Data=MD_Append out=MD_Append (Drop=_Name_)

prefix=MD;

var MD;

id RegYear;

by Site Year Season Type;

run;

 

proc append base=Work.md_hist_wd data=work.md_append;

run;


Accepted Solutions
Solution
‎05-24-2017 06:17 PM
Frequent Contributor
Posts: 103

Re: Repeat a section of code (multiple steps) for n iterations

[ Edited ]

Hi,

I'm not sure appending the dataset n-times is the most performant way to achieve what you want, but without further explanations or test data it's difficult to say.

Anyway one way to loop would be with a macro, something like this:

%macro repeat(n);
   %do i=1 %to &n.;
      data Sim_WD;
         set Sim_WD;
         Sim_value=COL1 + rand("Normal", 0, _RMSE_);
      run;

      PROC SQL;
         CREATE TABLE MD_append AS
            SELECT Site AS Site,
                   YEAR AS YEAR,
                   Season AS Season,
                   TYPE AS TYPE,
                   RegYear AS RegYear,
                   max(Sim_value) AS MD
            FROM Sim_WD
            GROUP BY Site,
                     YEAR,
                     Season,
                     TYPE,
                     RegYear
         ;
      QUIT;

      proc transpose Data=MD_Append out=MD_Append (Drop=_Name_)
         prefix=MD;
         var MD;
         id RegYear;
         by Site Year Season Type;
      run;    
    
      %if &i. eq 1 %then %do;
         data Work.md_hist_wd;
            set work.md_append;
         run;
      %end;
      %else %do;
         proc append base=Work.md_hist_wd data=work.md_append;
         run;
      %end;
   %end;
%mend repeat;
%repeat(100);

Cheers

 

________________________

- That still only counts as one -

View solution in original post


All Replies
Solution
‎05-24-2017 06:17 PM
Frequent Contributor
Posts: 103

Re: Repeat a section of code (multiple steps) for n iterations

[ Edited ]

Hi,

I'm not sure appending the dataset n-times is the most performant way to achieve what you want, but without further explanations or test data it's difficult to say.

Anyway one way to loop would be with a macro, something like this:

%macro repeat(n);
   %do i=1 %to &n.;
      data Sim_WD;
         set Sim_WD;
         Sim_value=COL1 + rand("Normal", 0, _RMSE_);
      run;

      PROC SQL;
         CREATE TABLE MD_append AS
            SELECT Site AS Site,
                   YEAR AS YEAR,
                   Season AS Season,
                   TYPE AS TYPE,
                   RegYear AS RegYear,
                   max(Sim_value) AS MD
            FROM Sim_WD
            GROUP BY Site,
                     YEAR,
                     Season,
                     TYPE,
                     RegYear
         ;
      QUIT;

      proc transpose Data=MD_Append out=MD_Append (Drop=_Name_)
         prefix=MD;
         var MD;
         id RegYear;
         by Site Year Season Type;
      run;    
    
      %if &i. eq 1 %then %do;
         data Work.md_hist_wd;
            set work.md_append;
         run;
      %end;
      %else %do;
         proc append base=Work.md_hist_wd data=work.md_append;
         run;
      %end;
   %end;
%mend repeat;
%repeat(100);

Cheers

 

________________________

- That still only counts as one -

PROC Star
Posts: 549

Re: Repeat a section of code (multiple steps) for n iterations

If you are interested in why it is not a good idea to repeat simulation code in a macro loop like this check out this article by Rick Wicklin:

 

http://blogs.sas.com/content/iml/2012/07/18/simulation-in-sas-the-slow-way-or-the-by-way.html

 

 

 

Super User
Super User
Posts: 7,392

Re: Repeat a section of code (multiple steps) for n iterations

Thats really not a good idea.  Basically you are repeating running that code 100 times, so 100 times the resources.  You could of course simplfy your code in one quick step:

data sim_wd_new;
  set sim_wd;
  do i=1 to 100;
    sim_value=col1 + rand("normal", 0, _rmse_);
    output;
  end;
run;

This will create 100 random values to the new file, i.e. multiply sim_wd * 100 records.  Then you have one transpose procedure and no need of the append.  Of course not being able to see the full picture this is only a suggestion.

Occasional Contributor jo1
Occasional Contributor
Posts: 14

Re: Repeat a section of code (multiple steps) for n iterations

Hi,

 

I take your point but I'm not sure what I need can be achieved another way.

 

To give you a better explanation of what I'm trying to do:  I'm calculating POE50 and POE10 values for electrical maximum demand data.  This sections of the code is doing the weather normalisation procedure.  I need to take the relationship (regression) between the max demand and the max/min temperature for a season and to apply that to all the historic weather data days I have for that site and then calculate the maximum demand for each year.  If I had 15 years of historic weather that would result in 15 maximum demands for that relationship.  I then need to add a random error to each of the historic weather data days (by taking a draw from a distribution with mean 0 and standard deviation equal to the standard error of the regression of the relationship) to produce another set of 15 maximum demands.  Each time this is done (100) I another set of maximum demands is added.  Then 10 and 50 POE demand values are taking from these resulting observations. I'm doing this for multiple sites etc.

 

I realise that this is doing a lot but it was currently done in excel with macros and I am converting it to SAS - this is a massive improvement Smiley Happy

 

Jo

Frequent Contributor
Posts: 103

Re: Repeat a section of code (multiple steps) for n iterations

Hi,

 

this improvement sounds good to me. You will need to evaluate the programming effort with the running time of your program and the gain in robustness or efficacy.

I would say, give it a try and come back to the community (with sample program & test data) if you need optimization feedback.

 

Cheers,

 

 

________________________

- That still only counts as one -

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 223 views
  • 4 likes
  • 4 in conversation