BookmarkSubscribeRSS Feed
ertr
Quartz | Level 8

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

9 REPLIES 9
Reeza
Super User

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;
ertr
Quartz | Level 8

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,

 

ertr
Quartz | Level 8

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

Reeza
Super User

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.

ertr
Quartz | Level 8

Here is my desired output,

 

Is it possible to do that?

 

Desired.png

 

Thanks

Reeza
Super User

Post it as text, that's not legible.

Reeza
Super User

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.

ertr
Quartz | Level 8

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

Reeza
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1409 views
  • 0 likes
  • 2 in conversation