I’m looking to see if Visual Analytics has a feature that can make what I was manually structuring on the interface side using some sort of time linking like parallel periods or the like without creating a new table via self joins prior to the interface layer.
I have an enrollment table that has one record per student per semester. I’d like to use the enrollment table to display if students are still enrolled in any given semester 30 semesters out. The display needs to be the number of students enrolled in the base enrollment semester, and the number of students still enrolled in the other semester and were enrolled in the base semester.
I created a table by hand with the fields (From_semeseter, To_semester, enrolled) then displayed the information in a tabular format. The structure was created by joining the enrollment table self-joining the enrollment table 30 times. The problem is that creating the structure maxes out my tiny system resources.
proc sql;
/*enrollment table*/
create table enrollment (
Student char(10),
Term_num int
);
insert into enrollment (student, term_num) values ('Bob', 1);
insert into enrollment (student, term_num) values ('Bob', 2);
insert into enrollment (student, term_num) values ('Bob', 4);
/*self joined enrollment*/
create table self_joined_enrollment (
Student char(10),
From_Semester int,
To_Semester int,
enrolled int
);
insert into self_joined_enrollment(student, From_semester, to_Semester, enrolled) values ('Bob', 1, 1, 1);
insert into self_joined_enrollment(student, From_semester, to_Semester, enrolled) values ('Bob', 1, 2, 1);
insert into self_joined_enrollment(student, From_semester, to_Semester, enrolled) values ('Bob', 1, 3, 0);
insert into self_joined_enrollment(student, From_semester, to_Semester, enrolled) values ('Bob', 1, 4, 1);
insert into self_joined_enrollment(student, From_semester, to_Semester, enrolled) values ('Bob', 2, 2, 1);
insert into self_joined_enrollment(student, From_semester, to_Semester, enrolled) values ('Bob', 2, 2, 1);
insert into self_joined_enrollment(student, From_semester, to_Semester, enrolled) values ('Bob', 2, 4, 1);
insert into self_joined_enrollment(student, From_semester, to_Semester, enrolled) values ('Bob', 3, 3, 0);
insert into self_joined_enrollment(student, From_semester, to_Semester, enrolled) values ('Bob', 3, 4, 0);
insert into self_joined_enrollment(student, From_semester, to_Semester, enrolled) values ('Bob', 4, 4, 1);
/*example of data displayed*/
select From_Semester, to_semester, sum(enrolled) as Students_Still_Enrolled
from self_joined_enrollment
where From_Semester =1 and to_semester=2;
quit;
... View more
