topic Re: How do i break total records into intervals of 30 minutes in SAS Programming

How do i break total records into intervals of 30 minutes

vicky07 — Sat, 16 May 2020 15:08:48 GMT

Hello,

I have a sample dataset to which I need to append current date and time(hh:mm) with interval of 30 minutes. The challenge is to break the total records into groups of 30 minutes between 9 am and 3 pm. So for example I have 1000 records in my data and I want to randomly take ~77(1000/13) and assign 05/16/2020 09:00 and the next batch of random 77 would get 05/16/2020 09:30 and the next would get 05/16/2020 10:00 and so on till 05/16/2020 15:00

Sample data

Data have;
do i = 1 to 1000;
ID = 123 * i;
ID_time = put(today(),mmddyys10.) ||''||put(time(),hhmm.);
drop i;
output;
end;
run;

Sample output:

ID ID_time
123 5/16/2020 9:00
246 5/16/2020 9:00
369 5/16/2020 9:00
492 5/16/2020 9:00
615 5/16/2020 9:00
738 5/16/2020 9:00
861 5/16/2020 9:30
984 5/16/2020 9:30
1107 5/16/2020 9:30
1230 5/16/2020 9:30
1353 5/16/2020 9:30
1476 5/16/2020 9:30
1599 5/16/2020 10:00
1722 5/16/2020 10:00
1845 5/16/2020 10:00
1968 5/16/2020 10:00
2091 5/16/2020 10:00
2214 5/16/2020 10:00

Thanks for your time and help!

Re: How do i break total records into intervals of 30 minutes

PaigeMiller — Sat, 16 May 2020 15:55:40 GMT

You would assign random numbers to each ID. Then once that is done, you can assign to the first 77 the date and time 05/16/2020 09:00, and so on.

data have2;
    set have;
    y=rand('uniform');
run;
proc sort data=have2;
    by y;
run;
data want;
    set have2;
    group=floor((_n_-1)/77);
    time = '16MAY2020:09:00:00'dt+30*60*group;
    format time datetime16.;
run;

Re: How do i break total records into intervals of 30 minutes

vicky07 — Sun, 17 May 2020 03:27:53 GMT

Thank you for your reply. Quick follow up questions, how do i change my date to 05/16/2020 format in the final output. Also, I want to display current date in my output instead of using hard coded date. Cold you please help with changing the date format and adding current date?

Thanks!

Re: How do i break total records into intervals of 30 minutes

RichardDeVen — Sun, 17 May 2020 03:38:15 GMT

The SURVEYSELECT Procedure provides a variety of methods for selecting probability-based random samples.

Example:

data have;
  do id = 1 to 1000;
    output;
  end;
run;

proc surveyselect noprint data=have out=groups groups=13;
run;

data want;
  set groups;
  timestamp = dhms(today(),8,30,0) + groupId * 1800;
  format timestamp dateAMPM.;
run;

proc freq data=want;
  table timestamp;
run;

Output

Re: How do i break total records into intervals of 30 minutes

PaigeMiller — Sun, 17 May 2020 11:21:01 GMT

@vicky07 wrote:

Thank you for your reply. Quick follow up questions, how do i change my date to 05/16/2020 format in the final output. Also, I want to display current date in my output instead of using hard coded date. Cold you please help with changing the date format and adding current date?

Thanks!

data want;
    set have2;
    group=floor((_n_-1)/77);
    today=today();
    hour='09:00't+1800*group;
    date_plus_time=catx(' ',put(today,mmddyys10.),put(hour,time5.));
    drop today hour group;
run;

Re: How do i break total records into intervals of 30 minutes

RichardDeVen — Sun, 17 May 2020 11:46:19 GMT

Just being picky, aren't there 12 intervals whose time ranges fall BETWEEN 9AM and 3PM. The 13th, or 3:00PM one ends at 3:30

Re: How do i break total records into intervals of 30 minutes

vicky07 — Sun, 17 May 2020 22:07:27 GMT

This is perfect. Thank you very much!!!

Re: How do i break total records into intervals of 30 minutes

vicky07 — Sun, 17 May 2020 22:17:04 GMT

Thank you very much,good to learn survey select method.

Re: How do i break total records into intervals of 30 minutes

vicky07 — Sun, 17 May 2020 22:19:32 GMT

You are right, the 3.00 pm one ends at 3.30.