topic Re: Create date variable weekly in SAS Programming

Create date variable weekly

lerdem — Wed, 03 Feb 2016 19:52:27 GMT

I have a tabel as below:

DAY SCHOOL_START_DAY SCHOOL_END_DAY

monday 11JAN2016 01MAY2016

tuesday 11JAN 2016 01MAY2016

WEDNESDAY 11JAN2016 01MAY2016 .....ETC

It means for first row school starts on 11jan2016 and every monday there is a class thrue 01may2016. For the second row school starts on 11jan2016 and every tueday there is a class thrue 01may2016.

i need a table shows each week like this one:

day school_start_day school_end_day class_days

monday 11jan2016 01may2016 11jan2016

monday 11jan2016 01may2016 18jan2016

monday 11jan2016 01may2016 25jan2016

monday 11jan2016 01may2016 01feb2016

.......etc

monday 11JAN2016 01MAY2016 25apr2016

TUeSDAY 11JAN2016 01MAY2016 12jan2016

TUeSDAY 11JAN2016 01MAY2016 19jan2016

TUeSDAY 11JAN2016 01MAY2016 26jan2016

TUeSDAY 11JAN2016 01MAY2016 2feb2016

...etc

is that possible to create second table?

Re: Create date variable weekly

data_null__ — Wed, 03 Feb 2016 20:09:19 GMT

data school;
   input day:$upcase12. (start end)(:date9.);
   format start end date9.;
   shift = whichc(day,'SUNDAY','MONDAY','TUESDAY','WEDNESDAY');
   i = intck(cats('week.',shift),start,end);
   do c = 0 to i;
      classDay = intnx(cats('week.',shift),start,c,'B');
      output;
      end;
   format classDay weekdate.;
   cards;
monday     11JAN2016  01MAY2016
tuesday    11JAN2016  01MAY2016
WEDNESDAY  11JAN2016  01MAY2016 
;;;;
   run;
proc print;
   run;

Re: Create date variable weekly

ballardw — Wed, 03 Feb 2016 20:13:12 GMT

If I understand what you want AND if your dates are SAS date values this might work:

data want;
   set have;
   do  class_days = schoold_start_day to school_end_day by 7;
      output;
   end;
   format classdays date9.;
run;

If your dates aren't SAS date values (why not:) ) but are character then

data want;
   set have;
   do  class_days = (input(school_start_day,date9.)) to (input(school_end_day,date9.)) by 7;
      output;
   end;
   format class_days date9.;
run;

Re: Create date variable weekly

lerdem — Wed, 03 Feb 2016 20:16:27 GMT

Thank you so much.

Quick question:

I have a big table for the first table i wrote:

cards;
monday 11JAN2016 01MAY2016
tuesday 11JAN2016 01MAY2016
WEDNESDAY 11JAN2016 01MAY2016

I can not write this one like that. I need to mention as table and variable name.??

Re: Create date variable weekly

data_null__ — Wed, 03 Feb 2016 20:40:51 GMT

My program is an example you can adapt it to a program that uses a SET statement. It is intended to show you the relevant funtions and loop logic needed to produce the output. I would expect you to look at the documentation for each of the functions and any other statements you are not familiar with. I am not a code writting service.

Re: Create date variable weekly

lerdem — Wed, 03 Feb 2016 20:50:08 GMT

For second part gave an error:

ERROR: Invalid DO loop control information, either the INITIAL or TO expression
is missing or the BY expression is missing, zero, or invalid.

Re: Create date variable weekly

ballardw — Wed, 03 Feb 2016 23:48:18 GMT

Which code generated the error? Post the exact code you ran as minor typos might generate that message. And post some actually rows of data that generated the error.

If you used the character form of the code example I posted and your data is not the same format then the appropriate format should be used. If the format is variable then possibly ANYDTDTE of some length. If your data is SAS values, such as would have a format of DATE9 or MMDDYY10 or such when you run Proc Contents on the input set then use the non-character version. It may help to post the results of running Proc Contents on your existing data.

Re: Create date variable weekly

lerdem — Thu, 04 Feb 2016 13:36:56 GMT

If it is not in write line like that. This one doesn't work, why?

data school;
input day:$upcase12. (start end)(:date9.);
format start end date9.;
shift = whichc(day,'SUNDAY','MONDAY','TUESDAY','THURSDAY','WEDNESDAY','FRIDAY');
i = intck(cats('week.',shift),start,end);
do c = 0 to i;
classDay = intnx(cats('week.',shift),start,c,'B');
output;
end;
format classDay weekdate.;
cards;
monday 11JAN2016 01MAY2016
tuesday 11JAN2016 01MAY2016
WEDNESDAY 11JAN2016 02MAY2016
WEDNESDAY 11JAN2016 01MAY2016
THURSDAY 15FEB2016 01MAY2016
monday 11JAN2016 02MAY2016

;;;;
run;

proc print;
run;

Re: Create date variable weekly

lerdem — Thu, 04 Feb 2016 14:58:35 GMT

data person;
input @1 day $8. @12 start DATE9. @22 end date9.;
format start DATE9. end date9.;

datalines;
FRIDAY 11JAN2016 01MAY2016
MONDAY 11JAN2016 01MAY2016
;
RUN;

data want;
set PERSON;
do DAYS = START to END by 7;
output;
end;
format DAYS date9.;
run;

I run this query it worked. But the days are not right. Says 11Jan2016 is FRIDAY. Please help

Re: Create date variable weekly

data_null__ — Thu, 04 Feb 2016 15:45:32 GMT

@lerdem wrote:

If it is not in write line like that. This one doesn't work, why?

data school;
input day:$upcase12. (start end)(:date9.);
format start end date9.;
shift = whichc(day,'SUNDAY','MONDAY','TUESDAY','THURSDAY','WEDNESDAY','FRIDAY');
i = intck(cats('week.',shift),start,end);
do c = 0 to i;
classDay = intnx(cats('week.',shift),start,c,'B');
output;
end;
format classDay weekdate.;
cards;
monday 11JAN2016 01MAY2016
tuesday 11JAN2016 01MAY2016
WEDNESDAY 11JAN2016 02MAY2016
WEDNESDAY 11JAN2016 01MAY2016
THURSDAY 15FEB2016 01MAY2016
monday 11JAN2016 02MAY2016

;;;;
run;

proc print;
run;

You have the calculation of shift wrong Wednesday comes before Thursday, should be this.

shift = whichc(day,'SUNDAY','MONDAY','TUESDAY','WEDNESDAY','THURSDAY','FRIDAY');

Otherwise the program works. I copied the code from from YOUR post and submitted it using SAS EG.

Re: Create date variable weekly

lerdem — Thu, 04 Feb 2016 15:57:12 GMT

OK, Last question

data school;
input day:$upcase12. (start end)(:date9.);
format start end date9.;
shift = whichc(day,'SUNDAY','MONDAY','TUESDAY','WEDNESDAY','THURSDAY','FRIDAY','SATURDAY');
i = intck(cats('week.',shift),start,end);
do c = 0 to i;
classDay = intnx(cats('week.',shift),start,c,'B');
output;
end;
format classDay weekdate.;
cards;
FRIDAY 11JAN2016 01MAY2016
MONDAY 11JAN2016 01MAY2016
SATURDAY 11JAN2016 01MAY2016
THURSDAY 11JAN2016 02MAR2016
THURSDAY 11JAN2016 01MAY2016
THURSDAY 15FEB2016 01MAY2016
THURSDAY 03MAR2016 01MAY2016
TUESDAY 11JAN2016 02MAR2016
TUESDAY 11JAN2016 01MAY2016
TUESDAY 03MAR2016 01MAY2016
WEDNESDAY 11JAN2016 01MAY2016
;;;;
run;

proc print;
run;

i keep gettin this error:

ERROR: Invalid DO loop control information, either the INITIAL or TO expression
is missing or the BY expression is missing, zero, or invalid.

thank you so much,appreciate your help.

Re: Create date variable weekly

data_null__ — Thu, 04 Feb 2016 16:15:09 GMT

@lerdem wrote:

OK, Last question

data school;
input day:$upcase12. (start end)(:date9.);
format start end date9.;
shift = whichc(day,'SUNDAY','MONDAY','TUESDAY','WEDNESDAY','THURSDAY','FRIDAY','SATURDAY');
i = intck(cats('week.',shift),start,end);
do c = 0 to i;
classDay = intnx(cats('week.',shift),start,c,'B');
output;
end;
format classDay weekdate.;
cards;
FRIDAY 11JAN2016 01MAY2016
MONDAY 11JAN2016 01MAY2016
SATURDAY 11JAN2016 01MAY2016
THURSDAY 11JAN2016 02MAR2016
THURSDAY 11JAN2016 01MAY2016
THURSDAY 15FEB2016 01MAY2016
THURSDAY 03MAR2016 01MAY2016
TUESDAY 11JAN2016 02MAR2016
TUESDAY 11JAN2016 01MAY2016
TUESDAY 03MAR2016 01MAY2016
WEDNESDAY 11JAN2016 01MAY2016
;;;;
run;

proc print;
run;

i keep gettin this error:

ERROR: Invalid DO loop control information, either the INITIAL or TO expression
is missing or the BY expression is missing, zero, or invalid.

thank you so much,appreciate your help.

I don't get any error. I suspect that "for you" for some reason the start and end dates are not being read correctly. Modify the code so that the data generation part does not run. Just after the input statement add

output; return;

and post the result of proc print of that data back here. Also show the log the entire relevant part not just the any error message. You are making this rather difficult with your cryptic description of the problem.

Re: Create date variable weekly

ballardw — Thu, 04 Feb 2016 16:28:57 GMT

Sorry, I made a bad assumption that the start date was the first day of the class. So the loop needs to be adusted to start on the correct day. I think this works. However you will need to provide the full name of the day of the week to read the day of week with this provided custom format. The example also does address a start date other than monday.

proc format library=work;
invalue dayofweek (upcase)
'SUNDAY'   = 1
'MONDAY'   = 2
'TUESDAY'  = 3
'WEDNESDAY'= 4
'THURSDAY' = 5
'FRIDAY'   = 6
'SATURDAY' = 7
;
RUN;


data person;
   informat day DAYOFWEEK. start  DATE9.  end date9.;
   input  day start  end ;
   format start DATE9. end date9.;
   if WEEKDAY(START) < day then do;
      start= intnx('day',start,day-weekday(start));
   end;
   if WEEKDAY(START) > day then do;
      start= intnx('day',start,day-weekday(start)+7);
   end;
datalines; 
FRIDAY 11JAN2016 01MAY2016
MONDAY 11JAN2016 01MAY2016
SUNDAY 11JAN2016 01MAY2016
MONDAY 13JAN2016 01MAY2016
;
RUN;