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 -
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.