DATA Step, Macro, Functions and more

Create date variable weekly

Reply
Contributor
Posts: 47

Create date variable weekly

[ Edited ]

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?

 

Respected Advisor
Posts: 3,799

Re: Create date variable weekly

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;

Capture.PNG

Contributor
Posts: 47

Re: Create date variable weekly

Posted in reply to data_null__

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.??

Respected Advisor
Posts: 3,799

Re: Create date variable weekly

[ Edited ]

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.

Contributor
Posts: 47

Re: Create date variable weekly

[ Edited ]
Posted in reply to data_null__

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;

 

Respected Advisor
Posts: 3,799

Re: Create date variable weekly

[ Edited ]

 

 


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.

Contributor
Posts: 47

Re: Create date variable weekly

Posted in reply to data_null__

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.

 

Respected Advisor
Posts: 3,799

Re: Create date variable weekly


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.

Super User
Posts: 11,343

Re: Create date variable weekly

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 notSmiley Happy ) 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;
Contributor
Posts: 47

Re: Create date variable weekly

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.

 

Super User
Posts: 11,343

Re: Create date variable weekly

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.

Contributor
Posts: 47

Re: Create date variable weekly

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

Super User
Posts: 11,343

Re: Create date variable weekly

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;
Ask a Question
Discussion stats
  • 12 replies
  • 959 views
  • 2 likes
  • 3 in conversation