Self Join on Time Series SAS Visual Analytics

[DavidPhillips2]

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;

[TedStolarczyk]

@DavidPhillips2 are you looking to do 'Difference from previous parallel period' or 'Percent difference from previous parallel period' calculations on your report data within SAS Visual Analytics?  If so, something like this? -> SAS Help Center: Working with Quick Calculations in a Report If so, it assumes you have a date or date/time data item for the calculation and that same date or date/time data item is also displaying within the visual where you want this calculation to display (e.g. Crosstab, List table, Bar chart, etc.).

[DavidPhillips2]

I looked over the documentation for these features.  I'm not seeing a feature that will allow the type of report I included with the example.

In time one a student is enrolled in time X then display 1.

A trickier one is if the student graduated before time X display 1.

[TedStolarczyk]

@DavidPhillips2 forgive me that I'm not following you now.  Any chance you can attach some sample input and expected output table mock-up?  You did type previously: "I'm not seeing a feature that will allow the type of report I included with the example."  I see SQL code but not a report example.  Regardless, if you need a more immediate answer at this point, feel free to call into our SAS Tech Support @ 919-677-8008.

[DavidPhillips2]

Ted, the SQL code produces all of the following that you asked for.

[TedStolarczyk]

@DavidPhillips2 my apologies I think because you first used the term "parallel periods" I jumped to wrong conclusions and was expecting date formatted data items.  Since SAS VA has a specific feature called "parallel periods" (among related calculations) when it comes to date formatted data items and thus the doc link I sent above (and subsequent sample data/report request).

[Stu_SAS]

Hi @DavidPhillips2, I think this should help solve it. Let's add some additional students to the data just so we can confirm that this is working as expected.

data students; 
    input student$ term_num;
    datalines;
Bob	1
Bob	2
Bob	4
Dan	2
Dan	3
Dan	4
Jon	3
Jon	4
;
run;

What it sounds like we want to do is count the number of distinct students between terms. We can do this using a distinct calculation and parameters. For example:

To do this:

1. Right-click student and select New Calculation. Use Distinct Count.
2. Drag in a table, a key value, and a slider as a page control
3. Add student and term_num to the table. Enable "Detail Data" on the table.
4. Add the distinct count of student to the key value
5. Add term_num to the slider

And that's it! It should dynamically count between the terms. 

 

[DavidPhillips2]

This solution might work.  I need to add some If logic to use this method for a particular business use case.  If the student graduated before the semester, do not count them as enrolled for any record of that student after the From semester   A student can have one or many degrees; I think I can include redundancy with degrees and still use this logic.   I'll look into this methodology tomorrow; hopefully, there will not be a complication with mixed aggregation levels in the if / distinct logic.   

[DavidPhillips2]

I can add graduated in general to the enrollment record by merging records together. I’m picturing graduated on the To semester. Then In theory i should be able to do if statements on the To semester. There is a business rule of graduating not counting if it is before the From semester, I might need to do that part of the logic on the dataset before VA.

[DavidPhillips2]

The methodology of using distinct with two parameters is very practical.  I'm sure I'll use it for something else.  Unfortunately, I need to know if a student was enrolled from 1 to a large number of semesters from their starting semester whether they have an enrollment record or not so it looks like I'm stuck creating a cross-joined structure, which is a small version of big data.