Assign a random weekday dates within range

Reply
Contributor
Posts: 44

Assign a random weekday dates within range

[ Edited ]

Hi, I have a data like this (sample data)

 

GroupBegdateenddate
A12-03-200525-08-2008
B03-06-200110-09-2003
C05-02-201121-06-2013

 

Now I want to have a variable "newdate" which is a random day between begdate and enddate. Also, this newdate has to be a weekday date (not saturday, sunday). Could anyone please show me how to do that? Thank you very much

Super User
Posts: 6,789

Re: Assign a random weekday dates within range

Posted in reply to trungcva112

There may be better ways, but here is something that works.

 

data all_weekdays;

set have;

original_record_num = _n_;

do day = begdate to enddate;

   random_num = ranuni(12345);

   if (2 <= weekday(day) <= 6) then output;

end;

run;

proc sort data=all_weekdays;

   by original_record_num random_num;

run;

data want;

set all_weekdays;

by original_record_num;

if first.original_record_num;

drop original_record_num random_num;

format day mmddyyd10.;

run;

 

The variable DAY is the one that contains the randomly selected weekday.

Contributor
Posts: 44

Re: Assign a random weekday dates within range

Posted in reply to Astounding

Hi. I tried your code, but each time I run it, it gave me the same day for each group. So the "day" is not random. How can we fix it?

Super User
Posts: 6,789

Re: Assign a random weekday dates within range

Posted in reply to trungcva112

Yes, the day is randomly selected.  If you want it to select a different random day each time you run the program, change the seed to the random number generator, using:

 

ranuni(0)

 

However, once you do that you can never replicate your results.  If asked to demonstrate that your results are correct, you will be unable to do so.

Valued Guide
Posts: 585

Re: Assign a random weekday dates within range

Posted in reply to trungcva112

Are those dates sas-dates in your dataset?

 

From the documentation of the outdated (see https://blogs.sas.com/content/iml/2013/07/10/stop-using-ranuni.html) ranuni-function:

The RANUNI function returns a number that is generated from the uniform distribution on the interval (0,1) [...]

You can use a multiplier to change the length of the interval and an added constant to move the interval. For example,

random_variate=a*ranuni(seed)+b; 

returns a number that is generated from the uniform distribution on the interval (b,a+b).

 

Because SAS-dates are numbers you can use begdate and enddate in the calculation. Replacing ranuni with rand('uniform') is recommended. The function weekday returns 1 for Sunday and 7 for Saturday, so you have to repeat calling rand-function until weekday returns 2-5.

Contributor
Posts: 44

Re: Assign a random weekday dates within range

Posted in reply to andreas_lds

Hi. Yes it is SAS date (numeric format). I tried your method but there is always a weekend dates.

 

Now suppose I have a list of dates for each group (that already filter all weekends, holiday etc)

Group Date
A02-01-02
A03-01-02
A04-01-02
A….
B
B
B
B

 

How can I pick 1 random date (random observation) for each group from this list. Is it easier to do?

Valued Guide
Posts: 585

Re: Assign a random weekday dates within range

Posted in reply to trungcva112

@trungcva112 wrote:

Hi. Yes it is SAS date (numeric format). I tried your method but there is always a weekend dates.

 

Now suppose I have a list of dates for each group (that already filter all weekends, holiday etc)

Group  Date
A 02-01-02
A 03-01-02
A 04-01-02
A ….
B
B
B
B

 

How can I pick 1 random date (random observation) for each group from this list. Is it easier to do?


@Astounding does exactly this.

Contributor
Posts: 44

Re: Assign a random weekday dates within range

Posted in reply to andreas_lds
But it gave me the same random date each time I run it
Super User
Posts: 10,284

Re: Assign a random weekday dates within range

Posted in reply to trungcva112

You will only get tested code when you supply example data against which the code can be tested. Post example data in a data step with datalines. A macro to convert existing datasets to data steps can be found here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

How to post code: https://communities.sas.com/t5/Getting-Started/How-to-add-SAS-syntax-to-your-post/ta-p/224394

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Valued Guide
Posts: 585

Re: Assign a random weekday dates within range

Posted in reply to trungcva112

See the documentation of ranuni, the same random numbers are generated if parameter seed is constant, to avoid this use 0, instead of 12345.

Super User
Posts: 10,788

Re: Assign a random weekday dates within range

Posted in reply to trungcva112
data have;
infile cards expandtabs;
input Group	$ Begdate : ddmmyy10. enddate : ddmmyy10. ;
format Begdate enddate  ddmmyy10. ;
cards;
A	12-03-2005	25-08-2008
B	03-06-2001	10-09-2003
C	05-02-2011	21-06-2013
;
run;
data want;
 if _n_=1 then do;
   declare hash h();
   h.definekey('n');
   h.definedata('date');
   h.definedone();
 end;
set have;
n=0;h.clear();
do date=begdate to enddate;
  if weekday(date) not in (1 7) then do;n+1;h.add();end;
end;
n=ceil(h.num_items*rand('uniform'));
h.find();
format date ddmmyy10.;
drop n ;
run;
Contributor
Posts: 44

Re: Assign a random weekday dates within range

@Ksharp
Thank you very much for your help. But it seems that your code select dates randomly with no replacement. Could you show me how to choose random dates with replacement?

I am truly appreciate your support
Super User
Posts: 10,788

Re: Assign a random weekday dates within range

Posted in reply to trungcva112

I don't understand what you mean. My code indeed did replace sample.

 

n=ceil(h.num_items*rand('uniform'));

this code randomly pick up a date from the valid weekday . 

Respected Advisor
Posts: 4,743

Re: Assign a random weekday dates within range

[ Edited ]
Posted in reply to trungcva112

@trungcva112

Or here a combination of what @Astounding and @Ksharp posted:

data have;
  infile cards expandtabs;
  input Group $ Begdate : ddmmyy10. enddate : ddmmyy10.;
  format Begdate enddate  ddmmyy10.;
  cards;
A 12-03-2005  25-08-2008
B 03-06-2001  10-09-2003
C 05-02-2011  21-06-2013
;
run;

data want(drop=_:);
  set have;
  format ran_date ddmmyy10.;
  do _i=1 to 1000;
    ran_date=Begdate +  floor(rand('uniform')*(enddate-Begdate+1));
    if weekday(ran_date) not in (1,7) then leave;
    else call missing(ran_date);
  end;
run;

N.B: The code above includes random selection of a date which is exactly the begin or end date (=borders included).

 

Super User
Posts: 10,788

Re: Assign a random weekday dates within range

Patrick,

That is not rigorously random sample  yet .

Ask a Question
Discussion stats
  • 17 replies
  • 180 views
  • 6 likes
  • 7 in conversation