topic Re: Assign specific value to a group of variables in SAS Programming

Assign specific value to a group of variables

trungcva112 — Fri, 13 Jul 2018 10:26:22 GMT

Hi everyone. I have an initial dataset like this

data trades; 
input ticker date : monyy7. B S;
format date monyy7.;
 cards;
99999  JAN2001 90 40
99999  FEB2001 40 90
88888  MAY2002 40 70
88888  JUN2002 40 40
 ;
run;

Now I want to create 3 variables: alpha, delta, gamma.

For each ticker-date, the three variables (alpha, delta, gamma) randomly take values from the set {0.1, 0.3, 0.5, 0.7, 0.9}, one at a time.

So for each ticker-date pair, there are 125 combinations of alpha, delta, gamma (there are 5 choices for alpha, followed by 5 choices for delta, followed by 5 choices for gamma ==> total = 5^3 combinations for each ticker-date)

So the output dataset is like this

Ticker	Date	B	S	Alpha	Delta	Gamma
99999	Jan2001	90	40	0.1	0.1	0.1
99999	Jan2001	90	40	0.1	0.1	0.3
99999	Jan2001	90	40	0.1	0.1	0.5
99999	Jan2001	90	40	0.1	0.1	0.7
99999	Jan2001	90	40	0.1	0.1	0.9
99999	Jan2001	90	40	0.1	0.3	0.1
99999	Jan2001	90	40	0.1	0.3	0.3
99999	Jan2001	90	40	0.1	0.3	0.5
99999	Jan2001	90	40	0.1	0.3	0.7
99999	Jan2001	90	40	0.1	0.3	0.9
…..	……..	………	……..	………	……	……….

Could anyone please help me how to do this? Thank you very much

Re: Assign specific value to a group of variables

FreelanceReinh — Fri, 13 Jul 2018 11:55:44 GMT

Hi @trungcva112,

The dataset containing all combinations (sorted by alpha, delta, gamma within each ticker-date pair) could be created using DO loops:

data want;
set trades;
do alpha=0.1, 0.3, 0.5, 0.7, 0.9;
  do delta=0.1, 0.3, 0.5, 0.7, 0.9;
    do gamma=0.1, 0.3, 0.5, 0.7, 0.9;
      output;
    end;
  end;
end;
run;

To have alpha, delta and gamma randomly take values, a random number function would be applied at some point, but this doesn't seem to be what you want.

Re: Assign specific value to a group of variables

PeterClemmensen — Fri, 13 Jul 2018 12:03:30 GMT

I don't understand this request: "For each ticker-date, the three variables (alpha, delta, gamma) randomly take values from the set {0.1, 0.3, 0.5, 0.7, 0.9}, one at a time.".

Afterwards you want to generate every possible combination of values for alpha, delta and gamma? In that regard, @FreelanceReinhs solution is solid.

If you want to assign values from the specified set {0.1, 0.3, 0.5, 0.7, 0.9} to alpha, delta and gamma, use the RAND Function with the Tables distribution. Some basic examples are given in the article Simulate Categorical Data In SAS.

Re: Assign specific value to a group of variables

Ksharp — Fri, 13 Jul 2018 12:08:35 GMT

Cartesian Product.

data x;
do i=0.1, 0.3, 0.5, 0.7, 0.9;
 output;
end;
run;

data trades; 
input ticker date : monyy7. B S;
n+1;
format date monyy7.;
 cards;
99999  JAN2001 90 40
99999  FEB2001 40 90
88888  MAY2002 40 70
88888  JUN2002 40 40
 ;
run;

proc sql;
create table want as
 select a.*,b.i as alpha,c.i as beta,d.i as gamma
  from trades as a,x as b,x as c,x as d
   order by n,alpha,beta,gamma;
quit;

Re: Assign specific value to a group of variables

Kurt_Bremser — Fri, 13 Jul 2018 12:10:50 GMT

Another way to have fun with it: Use SQL's capabilty of building cartesian joins:

data trades; 
input ticker date : monyy7. B S;
format date monyy7.;
 cards;
99999  JAN2001 90 40
99999  FEB2001 40 90
88888  MAY2002 40 70
88888  JUN2002 40 40
 ;
run;

data source;
input value;
cards;
0.1
0.3
0.5
0.7
0.9
;
run;

proc sql;
create table want as
select * from
  trades,
  source (rename=(value=alpha)),
  source (rename=(value=delta)),
  source (rename=(value=gamma))
;
quit;

Edit: expanded the code with dataset trades.

Re: Assign specific value to a group of variables

trungcva112 — Fri, 13 Jul 2018 13:36:42 GMT

Hi draycut. Yes, I would like to generate all possible combination of values for alpha, delta and gamma. So each ticker-date pair must has 125 combinations, with no duplicate. I will try @FreelanceReinhards solution