assign a set of random numbers to a variable

[Anita_n]

Hello all,

I have a problem, I hope I can get some help over here.

I am tring to assign  numbers  0,1, 2, 4, 5, 6, 7 (pls note here values are without 3) to a variable y if this condition is fullfiled

if x="8000" and a="0" and y=" ". This schould take place in a data step

I tried the following code but it tends to output y= " " . I don't know if my if else do condition is not working. I will be glad for any help

data want;
set have;

if x= "8000" and a="0" and y= " " then
   do;
      y=rand("integer" 0, 7);
      output;
    end;
run;

I know this code will include the value 3 which I don't want, is there any way I can exempt the value 3? Anyway the code is not working properly may some can help. Thanks

[Kurt_Bremser]

Please post an example for dataset have. Is y character or numeric?

[Anita_n]

y is a character

[Kurt_Bremser]

Create an array that holds your set of values, and use the random number as basis for an index into this array:

data test;
array rands{7} _temporary_ (0,1,2,4,5,6,7);
do i = 1 to 100;
  value = rands{floor(rand('uniform')*7)+1};
  output;
end;
keep value;
run;

[PaigeMiller]

Use 

y=rand("integer",0, 6);

Upper limit being 6 instead of 7, then if a number 3 or larger is returned, add one to it. Y will be numeric, but you can change that if you wish.

[Anita_n]

but I need the number 7 also. Pls the code is giving me missings for all y values. I dont know why.

Is there a mistake in my code?

[PaigeMiller]

When 6 is the result from RAND and you add one to it (as I said earlier), you get 7.

[Anita_n]

sorry, this is suppose to work for about 18,000 datasets that is why the do loop. it should output the results according to the given condition. so the results shouldn't only be the value 6  but values 0,1,2, 4, 5, 6, 7

[PaigeMiller]

Here's the logic in detail

You want random numbers:

0, 1, 2, 4, 5, 6, 7

However RAND('integer',0,6) produces random numbers

0, 1, 2, 3, 4, 5 , 6

which is not what you want.

Then when you add 1 to values 3 and larger you get random numbers

0, 1, 2, 4, 5, 6, 7

which is exactly what you want

[koyelghosh]

This is not tested. So may not work but you can try.
After if then try the following.
Y=rand('integer',0,7);
Do while (Y=3);
Y=Rand('integer',0,7);
End;
Output;
End;
Run;

[Anita_n]

ok let my try that

[koyelghosh]

Sorry there is an extra end after output. Please delete that

[koyelghosh · Accepted solution]

I am sorry I could not test the code (that I suggested) as I was away from my desktop.

There have been very nice alternative solution in this post, using arrays. They are elegant codes and will work here. However, I am doing some cheap selling of my method here. Apologies for that but thought you should know. May be useful in some cases.

Suppose the problem becomes complex where you have to exclude "one" number (say 3) from a large set of possible numbers (say 1 to 1000), then creating an array of 1000 numbers could be a little time consuming and memory consuming. However if you you use a DO WHILE loop, it will work well.

Supposing I want to create a Y column with random number ranging from 1 to 1000 but number 3 excluded, then I will write the below code.

DATA Number_3_Exluded_From_1_to_1000;
	SET sashelp.cars;
	Y=RAND('integer', 1, 1000);

	DO WHILE(Y=3);
		Y=RAND('integer', 1, 1000);
	END;
RUN;

When you run this code it has following CPU and memory footprints.

 NOTE: There were 428 observations read from the data set SASHELP.CARS.
 NOTE: The data set WORK.NUMBER_3_EXLUDED_FROM_1_TO_1000 has 428 observations and 16 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       user cpu time       0.01 seconds
       system cpu time     0.00 seconds
       memory              840.96k
       OS Memory           28584.00k
       Timestamp           06/25/2019 03:32:00 PM
       Step Count                        30  Switch Count  2
       Page Faults                       0
       Page Reclaims                     168
       Page Swaps                        0
       Voluntary Context Switches        10
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           264

The number 3 has been excluded from the column Y. You can check for that using a simple PROC SQL as below.

PROC SQL;
	SELECT Y 
	FROM Number_3_Exluded_From_1_to_1000 
	WHERE Y=3;
QUIT;

The log clearly says that no rows were selected as below.

 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 70         
 71         PROC SQL;
 72         SELECT Y
 73         FROM Number_3_Exluded_From_1_to_1000
 74         WHERE Y=3;
 NOTE: No rows were selected.
 75         QUIT;

I might be wrong here and I am sure there is a more elegant solution to the problem I have stated above. I would love to learn something new on this.

So what I am suggesting to you is run the below code. Replace everything after the IF THEN statement (in your original post)

	Y=rand('integer', 0, 7);

	Do while (Y=3);
		Y=Rand('integer', 0, 7);
	End;
	Output;
RUN;

Best wishes.

[PaigeMiller]

Hi, @koyelghosh 

---

I might be wrong here and I am sure there is a more elegant solution to the problem I have stated above. I would love to learn something new on this.

So what I am suggesting to you is run the below code. Replace everything after the IF THEN statement (in your original post)

	Y=rand('integer', 0, 7);

	Do while (Y=3);
		Y=Rand('integer', 0, 7);
	End;
	Output;
RUN;

---

I have no doubt that this will work. However, it may execute a little longer than some of the other solutions, since 1/8th of the time, you will have to go through the DO WHILE loop, and 1/8 of those it will go through the DO WHILE loop a second time and so on. I don't remember the algebraic formula for the infinite sum of fractions + powers of those fractions like this, but in any event this will likely take about 13% longer than other solutions. If that doesn't bother you (and for small data sets it doesn't bother me, but for large data sets maybe that's an issue), then I consider this to be a valid solution.

[koyelghosh]

@PaigeMiller @Thank you
You definitely have a valid argument against the suggested approach. However I was thinking, as the length of the set of numbers to choose from increases, the probability that the excluded number will appear will decrease. Thus in very few cases, it will actually enter the loop and spend time there.
However you are perfectly right that for small list (like 0 - 7) excluding 3 will have significant overhead in terms of time spent in the while loop.
May be I should do some simulation to try to guess big O of my suggested approach, for different length of set of numbers from which to exclude (a single number). Regardless, your post definitely makes me pause and think.
Thank you once again.