topic Re: Repeat a section of code (multiple steps) for n iterations in SAS Programming

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

jo1 — Tue, 23 May 2017 06:11:09 GMT

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;

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

Oligolas — Tue, 23 May 2017 06:38:25 GMT

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

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

PeterClemmensen — Tue, 23 May 2017 06:43:07 GMT

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

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

RW9 — Tue, 23 May 2017 08:12:21 GMT

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.

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

jo1 — Tue, 23 May 2017 22:37:54 GMT

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 🙂

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

Oligolas — Wed, 24 May 2017 07:37:36 GMT

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,