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?
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;
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.??
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.
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;
@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.
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.
@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.
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;
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.
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.
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.