DATA Step, Macro, Functions and more

Assign Random Values for Each Variables and Get the Maximum Value

Reply
Contributor
Posts: 59

Assign Random Values for Each Variables and Get the Maximum Value

[ Edited ]

Hello everyone,

 

I have a sample data set as below, I want to assign Random values between zero and one for each variables but it should in a new variables. exceptionally, if the limit variables includes missing values then it will assign zero instead of Random values.

 

Sample Data Set

Data Have;
Length CUST_ID 8 DEFAULT_DATE 8 Limit_1 8 Limit_2 8 Limit_3 8;
Infile Datalines Missover;
Input CUST_ID DEFAULT_DATE  Limit_1 Limit_2 Limit_3;
Format  DEFAULT_DATE date9.;
Datalines;
0001 21000 10000 10500 11000 
0002 21031 . . .
0003 21062 20000 20500 21000 
0004 21092 . . . 
0005 21123 30000 30500 31000
0006 21153 . . .
0007 21184 40000 40500 41000 
0008 21215 . . .
0009 21243 50000 50500 51000 
00010 21274 . . . 
00011 21304 60000 60500 10000
00012 21335 . . .
;
Run;

Random Data Should like this;

 

Random.png

 

My real purpose is the get Limit value which is equal to Maximum Random Value. I tried to explain in the following data set.

 

Desired.png

 

I think this process can make by SAS codes but I'm not sure how to do, on the other hand, maybe there can be more pratic ways  to do this method without using SAS code. I'd be glad, if you help me.

 

Thank you

Super User
Posts: 23,293

Re: Assign Random Values for Each Variables and Get the Maximum Value

Here's what I think you want. Not sure what you're actually after here...

 

data want;
	set have;
	array limits(*) limit_1 - limit_3;
	array _rand(*) limit_Rand1-limit_rand3;

	do i=1 to dim(limits);
		if not missing(limits(i)) then
			_rand(i) = rand('uniform');
		else _rand(i) = 0;
	end;
run;
Contributor
Posts: 59

Re: Assign Random Values for Each Variables and Get the Maximum Value

Yes but I want to get the maximum value, I mean;

 

if limit_Rand_03 greater than limit_Rand_01 and limit_Rand_02 then, it should bring Limit_1's original value in a new column(MaxValue).

 

if limit_Rand_02 greater than limit_Rand_01 and limit_Rand_03 then, it should bring Limit_2s original value in a new column(MaxValue).

 

if limit_Rand_03 greater than limit_Rand_02 and limit_Rand_03 then, it should bring Limit_3s original value in a new column(MaxValue).

 

How can I do this?

 

Thank you,

 

Contributor
Posts: 59

Re: Assign Random Values for Each Variables and Get the Maximum Value

Actually, this will give the Maximum Value; (By the way, is there a way to write this more easy->MAX(Limit_Rand1,Limit_Rand2,Limit_Rand3)

 

PROC SQL; 
Create Table Want2 As
SELECT *, MAX(limit_Rand1,limit_rand2,limit_rand3) As MaxVal From Want;
QUIT;

But I want to pull the limit value , which is eqaul to number of Rand variable, I mean,

 

if limit_Rand_03 greater than limit_Rand_01 and limit_Rand_02 then, it should bring Limit_1's original value in a new column(MaxValue).

 

if limit_Rand_02 greater than limit_Rand_01 and limit_Rand_03 then, it should bring Limit_2s original value in a new column(MaxValue).

 

if limit_Rand_03 greater than limit_Rand_02 and limit_Rand_03 then, it should bring Limit_3s original value in a new column(MaxValue).

 

Thank you

Super User
Posts: 23,293

Re: Assign Random Values for Each Variables and Get the Maximum Value

Post the expected output for your data. It sounds like you just want a random value from the 3 values? Is that what you're trying to do here? If so, I would use RANDTABLE instead. 

 

You can use MAX() to find the maximum values.

You can use WHICHN to find the index of that value.

Then you can use that index to find the value in the original array.

 

Or you can use RANDTABLE or RAND to generate a random number between 1 and 3 and just use that instead.

Contributor
Posts: 59

Re: Assign Random Values for Each Variables and Get the Maximum Value

Here is my desired output,

 

Is it possible to do that?

 

Desired.png

 

Thanks

Super User
Posts: 23,293

Re: Assign Random Values for Each Variables and Get the Maximum Value

Post it as text, that's not legible.

Super User
Posts: 23,293

Re: Assign Random Values for Each Variables and Get the Maximum Value

actually, it doesn't matter, the answer is the same:

 

You can use MAX() to find the maximum values.

You can use WHICHN to find the index of that value.

Then you can use that index to find the value in the original array.

 

This is much easier in a data step. If you need further help, post your code and log and current output.

Contributor
Posts: 59

Re: Assign Random Values for Each Variables and Get the Maximum Value

It seems okay, does it also seem okay for you?

 

Any suggestion?

 

 

data want;
	set have;
	array limits(*) limit_1 - limit_3;
	array _rand(*) limit_Rand1-limit_rand3;

	do i=1 to dim(limits);
		if not missing(limits(i)) then
			_rand(i) = rand('uniform');
		else _rand(i) = 0;
	end;
			MaxVal=Max(of _rand(*));
			IndexValue=WhichN(MaxVal,of _rand(*));
			DesiredValue=limits(IndexValue);

	drop i;
run;

Thanks

Super User
Posts: 23,293

Re: Assign Random Values for Each Variables and Get the Maximum Value

I still think it's overkill. If you're trying to randomly select a value - and maximum isn't really true, it's just a random value from the 3.

 

data want;
	set have;
	array limits(*) limit_1 - limit_3;
        index = rantbl(25, 1/3, 1/3, 1/3);
        desired_value = limits(index);
run;
Ask a Question
Discussion stats
  • 9 replies
  • 204 views
  • 0 likes
  • 2 in conversation