Help using Base SAS procedures

How to get the data based on weekdays?

Reply
Regular Contributor
Posts: 168

How to get the data based on weekdays?

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.

Super User
Super User
Posts: 7,392

Re: How to get the data based on weekdays?

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;

Regular Contributor
Posts: 168

Re: How to get the data based on weekdays?

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.

Super User
Super User
Posts: 7,392

Re: How to get the data based on weekdays?

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.

Regular Contributor
Posts: 168

Re: How to get the data based on weekdays?

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.

Super User
Posts: 6,928

Re: How to get the data based on weekdays?

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
New Contributor Vix
New Contributor
Posts: 4

Re: How to get the data based on weekdays?

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;


Regular Contributor
Posts: 168

Re: How to get the data based on weekdays?

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

New Contributor
Posts: 4

Re: How to get the data based on weekdays?

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:

1Raj025AUG201429AUG2014
2Ravi101SEP201405SEP2014
3Ram208SEP201412SEP2014
4Raj014SEP201418SEP2014
5Ravi121SEP201425SEP2014
6Ram228SEP201402OCT2014
7Raj004OCT201408OCT2014
8Ravi111OCT201415OCT2014
9Ram218OCT201422OCT2014
10Raj024OCT201428OCT2014
11Ravi131OCT201404NOV2014
12Ram207NOV201411NOV2014
13Raj013NOV201417NOV2014
14Ravi120NOV201424NOV2014
15Ram227NOV201401DEC2014
16Raj003DEC201407DEC2014
17Ravi110DEC201414DEC2014
18Ram217DEC201421DEC2014
19Raj023DEC201427DEC2014
20Ravi130DEC201403JAN2015

proc print; quit;

Super User
Posts: 6,928

Re: How to get the data based on weekdays?

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Regular Contributor
Posts: 168

Re: How to get the data based on weekdays?

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.

Super User
Posts: 6,928

Re: How to get the data based on weekdays?

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Regular Contributor
Posts: 168

Re: How to get the data based on weekdays?

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

Super User
Posts: 6,928

Re: How to get the data based on weekdays?

My code from

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Regular Contributor
Posts: 168

Re: How to get the data based on weekdays?

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

Ask a Question
Discussion stats
  • 23 replies
  • 735 views
  • 0 likes
  • 5 in conversation