BookmarkSubscribeRSS Feed
lerdem
Quartz | Level 8

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?

 

12 REPLIES 12
data_null__
Jade | Level 19
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

lerdem
Quartz | Level 8

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

data_null__
Jade | Level 19

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.

lerdem
Quartz | Level 8

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;

 

data_null__
Jade | Level 19

 

 


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

lerdem
Quartz | Level 8

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.

 

data_null__
Jade | Level 19

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

ballardw
Super User

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;
lerdem
Quartz | Level 8

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.

 

ballardw
Super User

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.

lerdem
Quartz | Level 8

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

ballardw
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 3125 views
  • 2 likes
  • 3 in conversation