topic Re: How to get the data based on weekdays? in SAS Procedures

How to get the data based on weekdays?

RamKumar — Thu, 07 Aug 2014 09:17:00 GMT

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.

Re: How to get the data based on weekdays?

RW9 — Thu, 07 Aug 2014 09:29:45 GMT

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;

run;

Re: How to get the data based on weekdays?

Kurt_Bremser — Thu, 07 Aug 2014 09:32:49 GMT

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;

Re: How to get the data based on weekdays?

Vix — Thu, 07 Aug 2014 11:50:19 GMT

data want (drop=j); CALL SYMPUTX('DATE1', '01JAN2014'D); do name= "RAJ", "RAVI", "RAM"; DO J=0 TO 52; START_DATE= PUT(INTNX('WEEK',SYMGET('DATE1'),J,'B') +1, DATE9.); END_DATE = PUT(INTNX('WEEK',SYMGET('DATE1'),J,'E')-1, DATE9.); OUTPUT; END; END; RUN;

Re: How to get the data based on weekdays?

RamKumar — Tue, 12 Aug 2014 13:52:24 GMT

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.

Re: How to get the data based on weekdays?

RW9 — Tue, 12 Aug 2014 14:18:02 GMT

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.

Re: How to get the data based on weekdays?

RamKumar — Wed, 13 Aug 2014 05:05:53 GMT

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.

Re: How to get the data based on weekdays?

Kurt_Bremser — Wed, 13 Aug 2014 06:22:51 GMT

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.

Re: How to get the data based on weekdays?

Vix — Wed, 13 Aug 2014 08:16:42 GMT

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;

Re: How to get the data based on weekdays?

RamKumar — Thu, 14 Aug 2014 06:45:14 GMT

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.

Re: How to get the data based on weekdays?

Kurt_Bremser — Thu, 14 Aug 2014 07:13:26 GMT

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.

Re: How to get the data based on weekdays?

RamKumar — Thu, 14 Aug 2014 12:16:55 GMT

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

Re: How to get the data based on weekdays?

Kurt_Bremser — Thu, 14 Aug 2014 12:28:46 GMT

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)

Re: How to get the data based on weekdays?

RamKumar — Thu, 14 Aug 2014 12:48:06 GMT

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

Re: How to get the data based on weekdays?

RW9 — Thu, 14 Aug 2014 13:08:27 GMT

Errrm, then just add format into mine?

data want (drop=j);

format start_date end_date date9.;

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;

run;

Re: How to get the data based on weekdays?

RamKumar — Thu, 14 Aug 2014 13:29:04 GMT

thanks - Format is fine. But I need the output as I mentioned earlier.

Re: How to get the data based on weekdays?

RW9 — Thu, 14 Aug 2014 13:33:11 GMT

Ah, I see. Its three day gaps or something similar. Check out the SAS docs: SAS(R) 9.3 Functions and CALL Routines: Reference

You can use DAY3 or whatever your interval is, and change the J loop to be how many times you want it:

data want (drop=j);

format start_date end_date date9.;

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

do j=0 to 3;

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

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

output;

end;

run;

Re: How to get the data based on weekdays?

RamKumar — Thu, 14 Aug 2014 14:08:08 GMT

I think you've misunderstood my question. I agree with you, but I looking for end date is startdate+4 days and not 06jun2014.

i need the output as below

Re: How to get the data based on weekdays?

RW9 — Thu, 14 Aug 2014 14:20:21 GMT

data want (drop=j);

format start_date end_date date9.;

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

do j=0 to 3;

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

end_date=intnx('day3',start_date,j); /* Think you can change this to day4 if that's whats needed */

output;

end;

run;

Re: How to get the data based on weekdays?

RamKumar — Thu, 14 Aug 2014 14:44:55 GMT

Thanks for your suport and patience. But still it is now working as i expect.

See the output below for your code after I changed day 3 to day4 in end_date variable

obs start_date end_date name

1 04FEB2014 02FEB2014 Raj

2 07FEB2014 10FEB2014 Raj

3 10FEB2014 18FEB2014 Raj

4 13FEB2014 22FEB2014 Raj

5 04FEB2014 02FEB2014 Rav

6 07FEB2014 10FEB2014 Rav

7 10FEB2014 18FEB2014 Rav

8 13FEB2014 22FEB2014 Rav

9 04FEB2014 02FEB2014 Ram

10 07FEB2014 10FEB2014 Ram

11 10FEB2014 18FEB2014 Ram

12 13FEB2014 22FEB2014 Ram