Advanced SAS Query

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Advanced SAS Query

I have a data set with courses and times slots.  I need to create a new variable to assign the courses to all applicable times slots for each applicable day.  Currently, if I use a case when or a if/then statement, each row is only allowed in one category/level of the new variable.  I need code so that under the specified conditions, the course time slot will be in each level appropriate.

 

My data looks like this:

begin_timeend_timebuildingroommeeting_days
80010401100MW
80010102200MW

 

I want it to look like this:

 

time_of_dayDay_of_weekBuildingroomBegin_timeEnd_Time
8:00 AMMonday11008001040
8:00 AMMonday22008001010
9:00 AMMonday11008001040
9:00 AMMonday22008001010
10:00 AMMonday11008001040
10:00 AMMonday22008001010
8:00 AMWednesday11008001040
8:00 AMWednesday22008001010
9:00 AMWednesday11008001040
9:00 AMWednesday22008001010
10:00 AMWednesday11008001040
10:00 AMWednesday22008001010

 

 

So that the begin and end times are in their appropriate hourly time slots.  Time_of_day and day_of_week are new variables, day_of_week is derived from the meeting_days variable (M=Monday W=Wednesday) and time_of_day is just an hourly time variable.

 

I was trying to do this on one step rather than creatingn a bunch of small data sets and then appending them.  Any ideas on how to get this in one step?

 

Thanks!


Accepted Solutions
Solution
‎05-23-2016 10:58 AM
Grand Advisor
Posts: 17,325

Re: Advanced SAS Query

You need a data step, there's no recursion, there's a nested loop.

This should get you started.

 

Some things you'll need to modify:

1. Convert times to SAS times

2. Days are currently initialled, ie M/W, you'll need to map them to days of the week. I recommend a format. 

3. Drop variables that aren't needed, I've left everything in for now. 

 

Thanks to @paulkaefer for the sample data. 

 

data classes;
    infile datalines delimiter=',';
    informat begin_time end_time time.;
    format begin_time end_time time.;
    input begin_time end_time building room    meeting_days $;
    datalines;
08:00,10:40,1,100,MW
08:25,10:10,2,200,MW
;



data want; 
set classes;

ndays = length(meeting_days);*calculate number of days;

do i=1 to ndays; *loop over days;
day=char(meeting_days, i);
	do time=intnx('hour', begin_time, 0, 'b') to 
		intnx('hour', end_time, 0, 'e') by 3600; *loop over hours;
	output;
	end;
end;

format time time.;
run;

proc print;run;

View solution in original post


All Replies
Grand Advisor
Posts: 17,325

Re: Advanced SAS Query

You need two do loops, one to loop over hours and one then days and then an explicit output statement. 

 

Do all courses start on the hour?

 

 

Occasional Contributor
Posts: 5

Re: Advanced SAS Query

All courses do not start on the hour.  At the moment, I am using proc sql and doing the following:

 

case when  ("0800" between begin_time and end_time) or ("0859" between begin_time and end_time)  then  8:00 am"

 

etc. 

 

The do loop recursively creates the table based on the variables, correct?  That is what I was thinking but I couldnt figure out how to set it up.  Im guessing I need to do this in a data step rather than proc sql?

 

Thanks!

Solution
‎05-23-2016 10:58 AM
Grand Advisor
Posts: 17,325

Re: Advanced SAS Query

You need a data step, there's no recursion, there's a nested loop.

This should get you started.

 

Some things you'll need to modify:

1. Convert times to SAS times

2. Days are currently initialled, ie M/W, you'll need to map them to days of the week. I recommend a format. 

3. Drop variables that aren't needed, I've left everything in for now. 

 

Thanks to @paulkaefer for the sample data. 

 

data classes;
    infile datalines delimiter=',';
    informat begin_time end_time time.;
    format begin_time end_time time.;
    input begin_time end_time building room    meeting_days $;
    datalines;
08:00,10:40,1,100,MW
08:25,10:10,2,200,MW
;



data want; 
set classes;

ndays = length(meeting_days);*calculate number of days;

do i=1 to ndays; *loop over days;
day=char(meeting_days, i);
	do time=intnx('hour', begin_time, 0, 'b') to 
		intnx('hour', end_time, 0, 'e') by 3600; *loop over hours;
	output;
	end;
end;

format time time.;
run;

proc print;run;

Regular Contributor
Posts: 154

Re: Advanced SAS Query

[ Edited ]

This is a great opportunity to use an output; statement combined with an IF statement: you can output multiple rows (even to different datasets) within one iteration of a DATA step.

 

There may be a better way to handle times, but this works.

 

Here's my code, with your sample dataset (edited to included a do loop):

 

data classes;
    infile datalines delimiter=',';
    input begin_time end_time building room    meeting_days $;
    datalines;
800,1040,1,100,MW
800,1010,2,200,MW
;

data classes_modified(drop=iHour);
    set classes;
    format time_of_day hhmm. Day_of_week $9.;
    do iHour = 800 to 1700 by 100;
        if (begin_time <= iHour <= end_time) then do;
            time_of_day = iHour * 60 * 60 / 100;
            if index(meeting_days, 'M') then; do;
                Day_of_week = "Monday";
                output;
            end;
            if index(meeting_days, 'W') then; do;
                Day_of_week = "Wednesday";
                output;
            end;
        end;
    end;
run;

 

Occasional Contributor
Posts: 5

Re: Advanced SAS Query

Thank you! I will give this a try!!!

Regular Contributor
Posts: 154

Re: Advanced SAS Query

Note that I edited it just before you replied.

Occasional Contributor
Posts: 5

Re: Advanced SAS Query

Worked like a charm!! Thank you for all of the help!!!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 390 views
  • 0 likes
  • 3 in conversation