How to get the data based on weekdays?

[RamKumar]

I need to create a schedule for three persons Raj, Ravi and Ram to download the daily trigger files.  The schedule needs to cover a year, 2014.

There are 3 columns, start date, end date and name.

Start date is always a Monday and end date is always a Friday.  

The order that the names have to be in are Ram, Ravi then Raj.

The first start date is 6/2/2014 and end date is 6/6/2014, but the first dates you need to print are for the following week.

Output will look like this:

Obs    start_date      end_date     name

  1    06/09/2014    06/13/2014    Ram

  2    06/16/2014    06/20/2014    Ravi

  2    06/23/2014    06/27/2014    Ravi

I need this data from June to December 2014. It is OK if we receive the data from January.

[RW9]

Hi,

Try something like this (you can change the dates to be whatever you want):

data want (drop=j);

  do name="Raj","Ravi","Ram";

    do j=0 to 3;

      start_date=intnx('week','06FEB2014'd,j);

      end_date=intnx('week','06JUN2014'd,j);

      output;

    end;

  end;

run;

[RamKumar]

I tried your code, but it is not working and error is 'statement used out of order. near second line (do statement).

Please suggest me the other way around.

[RW9]

The code as posted runs correctly in my SAS session.  Please clarify what 'is not working' and what changes have been made to the code.

[RamKumar]

I've not done any changes to the program. I'm getting error in the line 'do name="Raj","Ravi","Ram" stating that 'statement used out of order near second line (do statement). any problem with my session or SAS version?

Please share me your output please.

[Kurt_Bremser]

What happens here:

16	data want (drop=j);
17	do name="Raj","Ravi","Ram";
18	do j=0 to 3;
19	start_date=intnx('week','06FEB2014'd,j);
20	end_date=intnx('week','06JUN2014'd,j);
21	output;
22	end;
23	end;
24	run;

NOTE: The data set WORK.WANT has 12 observations and 3 variables.

NOTE: DATA statement used (Total process time):

	real time	0.01 seconds
	cpu time	0.00 seconds

Please post your log.

[Vix]

Hi Ram kumar,

Please use my above code and it will work fine. Only add length statement to give exact length of name variable after data _null_ statement.

length $4;

[RamKumar]

Just I tested your code and it is producing 159 rows instead of 50-60 rows. I need the data like below.

When i ran your program, I got the output like,

                                                            start_

                                      Obs    name     date     end_date

                                        1    Raj      19756      19875

                                        2    Raj      19763      19882

                                        3    Raj      19770      19889

                                        4    Raj      19777      19896

                                        5    Rav      19756      19875

                                        6    Rav      19763      19882

                                        7    Rav      19770      19889

                                        8    Rav      19777      19896

                                        9    Ram      19756      19875

                                       10    Ram      19763      19882

                                       11    Ram      19770      19889

                                       12    Ram      19777      19896

But I need the output like,

                                                             start_

                                      Obs    name     date     end_date

                                        1    Raj       03feb2014   07feb2014

                                        2    Rav       10feb2014   14feb2014

                                        3   Ram       17feb2014   21feb2014

till last week of june or december

[HBruun]

So only 1 agent at work each week ? if so put rotation in your agent dataset.

*Make a dateset with your agents;

*rotation is the schedule if only one agent at work each week;

data agents;

input name $ rotation;

datalines;

Raj 0

Ravi 1

Ram 2

;

run;

data schedule;

set agents;

*start as monday;

*change today() to a another date if another start is needed like '01jan2014'd ;

start=intnx('week', today(), rotation)+1; * +1 to get monday;

do start_date = start to '31dec2014'd by 20;  *20 = 3 weeks rotation (3*7 days)-1;

   end_date=start_date +4;

output;

end;

format start_date end_date date9.;

drop start;

run;

proc sort; by start_date name ; run;

Result:

1	Raj	0	25AUG2014	29AUG2014
2	Ravi	1	01SEP2014	05SEP2014
3	Ram	2	08SEP2014	12SEP2014
4	Raj	0	14SEP2014	18SEP2014
5	Ravi	1	21SEP2014	25SEP2014
6	Ram	2	28SEP2014	02OCT2014
7	Raj	0	04OCT2014	08OCT2014
8	Ravi	1	11OCT2014	15OCT2014
9	Ram	2	18OCT2014	22OCT2014
10	Raj	0	24OCT2014	28OCT2014
11	Ravi	1	31OCT2014	04NOV2014
12	Ram	2	07NOV2014	11NOV2014
13	Raj	0	13NOV2014	17NOV2014
14	Ravi	1	20NOV2014	24NOV2014
15	Ram	2	27NOV2014	01DEC2014
16	Raj	0	03DEC2014	07DEC2014
17	Ravi	1	10DEC2014	14DEC2014
18	Ram	2	17DEC2014	21DEC2014
19	Raj	0	23DEC2014	27DEC2014
20	Ravi	1	30DEC2014	03JAN2015

proc print; quit;

[Kurt_Bremser]

proc format library=work;

value names

  0 = 'Ram'

  1 = 'Ravi'

  2 = 'Raj'

;

run;

data want (keep=start_date end_date name);

format

  start_date

  end_date

    mmddyy10.

  name $4.

;

i = 0;

do start_date = '09jun2014'd to '31dec2014'd by 7;

  end_date = start_date + 4;

  name = put(i,names.);

  output;

  i = mod(i+1,3);

end;

run;

[RamKumar]

Your code is perfect.Many thanks for your inputs.

But i could not understand the purpose of 'mod' function ( i = mod(i+1,3);) from your program.

[Kurt_Bremser]

This construct simply produces a recurring sequence of 0,1,2.

(mod is the modulo function).

mod(0+1,3) results in 1

mod(1+1,3) results in 2

mod(2+1,3) results in 0

I use this to decouple the "person" iteration from the "date" iteration

If you get another person for the schedule, just expand the names. format and use 4 in the mod function.

You can make the code completely static by reading the persons for the format from a dataset (store the number of records in a macro variable to be used in the mod function) and using macro variables for the beginning and end dates.

[RamKumar]

When i ran your program, I got the output like,

                                                            start_

                                      Obs    name     date     end_date

                                        1    Raj      19756      19875

                                        2    Raj      19763      19882

                                        3    Raj      19770      19889

                                        4    Raj      19777      19896

                                        5    Rav      19756      19875

                                        6    Rav      19763      19882

                                        7    Rav      19770      19889

                                        8    Rav      19777      19896

                                        9    Ram      19756      19875

                                       10    Ram      19763      19882

                                       11    Ram      19770      19889

                                       12    Ram      19777      19896

But I need the output like,

                                                             start_

                                      Obs    name     date     end_date

                                        1    Raj       03feb2014   07feb2014

                                        2    Rav       10feb2014   14feb2014

                                        3   Ram       17feb2014   21feb2014

till last week of june or dcember

[Kurt_Bremser]

My code from August 7, 2014 11:32 has the formats assigned. My test run of RW9's code from August 13, 2014 08:22 does have no formats, which yields the 5-digit numbers that are actually days after 01jan1960 (standard for SAS dates)

[RamKumar]

Your code is perfect. But I would like achieve the same with intnx function meant via RW9's code. Please suggest.