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;
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
- Cheers -
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
- Cheers -
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
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.
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 🙂
Jo
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,
- Cheers -
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.