SAS simulate with random variable

[schnuz]

1) Table1 has Col1 and Col2

2) Table2 is distinct Col1 as Col3 and generated random variable Col4

3) Simulate a function Col2*2 + Col4 where Col4 is based on Table1.Col1 = Table2.Col3

4) Col4 is diff for each trial

5) I don't really know how to do this but tried the below. Any help will be really appreciated.

data monte_carlo;
%let num = 10;
do i = 1 to #
	do while(not eof);
		set Table2 end=eof;
		Col4 = rand('uniform'); 
		put Col4;
	end;
	do while(not eof);
		set Table1 end=eof;
		Col2*2 + Col4;
	end;
end;
run;2 2 

[schnuz]

---

delete and repost

[ballardw]

I do not see any description of how Col4 depends on other variable(s).

Please do not post the description of your problem in the code or text boxes as those do not wrap text and it is extremely hard to read a narrative. The code/text box is for code and text data. The main message window is for long narrative.

[schnuz]

Hi, apologies for the formatting as I'm new here. Will repost my question.

1) Table1 has Col1 and Col2

2) Table2 is distinct Col1 as Col3 and generated random variable Col4

3) Simulate a function Col2*2 + Col4 where Col4 is based on Table1.Col1 = Table2.Col3

4) Col4 is diff for each trial

5) I don't really know how to do this but tried the below. Any help will be really appreciated.

data monte_carlo;
%let num = 10;
do i = 1 to #
	do while(not eof);
		set Table2 end=eof;
		Col4 = rand('uniform'); 
		put Col4;
	end;
	do while(not eof);
		set Table1 end=eof;
		Col2*2 + Col4;
	end;
end;
run;

[FreelanceReinh]

Hello @schnuz and welcome to the SAS Support Communities!

Do you want something like this?

/* Create sample data for demonstration */

data table1;
input col1 $ col2;
cards;
A 1
A 2.3
B 5
B 0.7
C 8
D 1.6
;

%let num=10; /* number of simulations */

/* Prepare lookup table of random numbers */

data rand_list;
call streaminit(27182818);
retain i;
set table1(keep=col1);
by col1;
if first.col1;
do i=1 to #
  rv=rand('uniform');
  output;
end;
run;

proc sort data=rand_list;
by i;
run;

/* Replicate dataset TABLE1 to have &num copies */

proc surveyselect data=table1 noprint
  out=monte_carlo(rename=(replicate=i))
  rep=&num samprate=1;
run;

/* Simulate function */

data monte_carlo;
merge monte_carlo
      rand_list;
by i col1;
f=2*col2+rv;
drop rv; /* optional */
run;

Dataset RAND_LIST:

Obs     i    col1        rv

  1     1     A      0.5321
  2     1     B      0.0085
  3     1     C      0.8897
  4     1     D      0.0685
  5     2     A      0.4181
  6     2     B      0.5169
...
 39    10     C      0.6392
 40    10     D      0.1856

Dataset MONTE_CARLO:

Obs     i    col1    col2       f

  1     1     A       1.0     2.5321
  2     1     A       2.3     5.1321
  3     1     B       5.0    10.0085
  4     1     B       0.7     1.4085
  5     1     C       8.0    16.8897
  6     1     D       1.6     3.2685
  7     2     A       1.0     2.4181
  8     2     A       2.3     5.0181
  9     2     B       5.0    10.5169
 10     2     B       0.7     1.9169
...
 59    10     C       8.0    16.6392
 60    10     D       1.6     3.3856

[schnuz]

Thank you! I tried your solution and it works. However, I hit a problem when it's based on 3 tables.
I tried the following but have problem merging the 3rd table in as it's not sorted the same way.

So the function = Col3*2 + Col5 + Col7

proc sort data=table1 out= table1;
by col1;
run;

proc sort data=table1 out= table1_1;
by col2;
run;

%let num=10; /* number of simulations */

/* Prepare lookup table of random numbers for ABCD */
data rand_list1;
call streaminit(27182818);
retain i;
set table1(keep=col1);
by col1;
if first.col1;
do i=1 to #
  rv1=rand('uniform');
  output;
end;
run;

/* Prepare lookup table of random numbers for XYZ */
data rand_list1;
call streaminit(42);
retain j;
set table1(keep=col2);
by col2;
if first.col2;
do j=1 to #
  rv2=rand('uniform');
  output;
end;
run;

proc sort data=rand_list1;
by i;
run;

proc sort data=rand_list2;
by j;
run;

/* Replicate dataset TABLE1 to have &num copies */
proc surveyselect data=table1 noprint
  out=monte_carlo1(rename=(replicate=i))
  rep=&num samprate=1;
run;

/* Replicate dataset TABLE1_1 to have &num copies */
proc surveyselect data=table1_1 noprint
  out=monte_carlo2(rename=(replicate=j))
  rep=&num samprate=1;
run;

/* Simulate function */
data monte_carlo1;
merge monte_carlo1
      rand_list1;
by i col1;
run;

data monte_carlo2;
merge monte_carlo2
rand_list2;
by j col2;
run;

I reckon I need to sort and merge the 2 tables, however, i and j are not common. How should I proceed? 

Also Table1 is 15k records and I plan to simulate 100k trials. This seems to get really big.

Many thanks again.

[FreelanceReinh]

---

@schnuz wrote:

Table1 is 15k records and I plan to simulate 100k trials. This seems to get really big.

---

I see. Then you should turn to more efficient techniques, e.g., use a hash object for the lookup, as shown below.

data table1;
input col1 $ col2 $ col3;
cards;
A X 1
A Y 2.3
A Z 5
B X 0.7
B Z 8
C Y 1.6
C Y 5.6
D X 3.9
;

%let num=100000;

sasfile table1 load;

data monte_carlo;
call streaminit(27182818);
dcl hash h1();
h1.definekey('col1');
h1.definedata('rv1');
h1.definedone();

dcl hash h2();
h2.definekey('col2');
h2.definedata('rv2');
h2.definedone();

do i=1 to #
  do _n_=1 to n;
    set table1 nobs=n point=_n_;
    if h1.find() ne 0 then do;
      rv1=rand('uniform');
      h1.add();
    end;
    if h2.find() ne 0 then do;
      rv2=rand('uniform');
      h2.add();
    end;
    f=2*col3+rv1+rv2;
    output;
  end;
  h1.clear();
  h2.clear();
end;
stop;
drop rv:;
run;

sasfile table1 close;

[schnuz]

Thank you that works great! I have modified it to have a total that sums f and only output total. The method of doing this is beyond my SAS knowledge so thank you so much for helping and sharing your expertise. I have so much to learn.

One more question. Along the concept of VaR, if I want to find the total at 99% confidence level, do I use PROC FREQ? Or do I sort the total and calculate manually?

[FreelanceReinh]

You can compute percentiles, e.g., the lower and upper limit of an interval that contains 99% of the simulated totals (excluding only the lower and upper 0.5% tails of the empirical distribution), with PROC UNIVARIATE.

Example:

proc univariate data=sashelp.stocks;
var volume;
output out=perc pctlpts=0.5 99.5 pctlpre=p;
run;

Resulting dataset perc:

Obs      p0_5       p99_5

 1     4477828    140382836

[yabwon]

Hi,

do you want something like this:

data have;
input col1 $ col2;
cards;
a 1 
a 2.3
b 5
b 0.7
c 8
d 1.6
;
run;


%let num = 3;
data want;
  do until(last.col1);
    set have curobs=curobs;
    by col1; /* I assume that have is sorted by col1 */
    if first.col1 then start = curobs;
    if last.col1  then end = curobs;
  end;

  call streaminit(42);
  do i = 1 to &num.;
    col4 = rand('uniform');

    do point = start to end;
      set have point = point;
      y = col2 * 2 + col4;
      output;
    end;
  end;

  /* drop i start end; */
run;

Bart

[schnuz]

Hi Bart, thanks. I tried Freelance's solution first and it worked. I'm struggling with the problem if there are 3 tables instead of 2. Will try out your solution too later.