Crosstab Object - How can I create groupings of Measure Columns in a Report

[DaveShea]

Hi,

I have a report in SAS 9.4 that uses Proc TABULATE to summarise my demo data of exam results for high school students. Each student took several exams and either passed (1) or failed (0) a given exam.For the report, I need to visually group some exam types together (ie all the exams for languages).

I now need to wave a fond farewell to Proc TABULATE and re-create this report in SAS Visual Analytics.

Each student lives in a location of New Zealand and this is reported using the Area/Region/Site/SubSite values.

The report looks like this:

In Proc TABULATE I am able to form groups of Analysis variables by using the ALL keyword and concatenating the two sets of columns. In the above example, I have assigned the label "non-Languages" to the ALL covering Maths, Chemistry and Physics and the label "Languages" to the ALL covering English, French Spanish German.

n.b My source data does not contain a category variable of "Language" or "non-Language". Each Student's data exists on a single row only of the source dataset.

The SAS code attached to this post can be used to re-create the dataset used, WORK.Student_Scores.

What I need to do is produce a report similar to the Proc TABULATE report, but within SAS Visual Analytics, ideally in v7.3 but at a pinch in v7.4, but no higher, as my team do not have access to anything newer than v7.4.

The best that I can come up with in SAS Visual Analytics v7.4 is a Crosstab object that looks like this:

However, I cannot think of a way of assigning labels to visually group the columns in the same way as they appear in the Proc TABULATE report. Also, I would quite like to find a way of replacing the word "Total" in the Totals line, with something more meaningful, such as "Total Passes by Exam", but that s of secondary importance.

The VA report must be capable of using Aggregated Measures defined within the report, such as percentages, and so we must retain the one row per Student design of the source dataset.

If anyone has any bright ideas as to how I can achieve the visual grouping labels over the two sets of Measure columns I would be really grateful. I have attached the SAS code that generates the dataset that I have loaded into SAS/VA that drives the above Crosstab report.

Many thanks,

Downunder Dave

Wellington

[Basl]

Hi Dave,

If you right-click your "subjects" category, you should be able to make a custom category.

With the custom category you can sort the subjects the way you want. If you then add the custom category at the top of the crosstab and the subjects-category just below that, you should get what you want.

resultMaking custom category

I hope this helped you with your problem

[DaveShea]

Hi Basl,

Thank you for your interest and idea. Unfortunately, in my case I am looking for a way to visually group sets of measure columns rather than sets of category columns.

In your example, you have a category column [Sanction] which has a variety of values, such as [Pupil Conference], [Teacher Conference], [Parent Conference] and [After School Detention] etc. and you use this in your crosstab to create columns.

The [New Custom Category] facility ([Aangepaste categorie wijzigen]) allows you to group those [Sanction] categories into super-groups, which you then add to your report as [Aangepaste categorie 1].

In my example data, my columns are measure columns and I want to assign a label over the top of each group just to indicate to the report user what type of exam (Language or non-Language) the column fits under.

Does anyone else have any ideas ?

Thanks anyway.

Downunder Dave

Wellington

[FredrikE]

I don't think you can do it unless you modify the data before loading it into VA.

If you transform all measures to one dimension and one measure you can do the grouping as described above.

That is how I use to do it 🙂

//Fredrik

[DaveShea]

Hi Fredrik,

Thank you very much for your ideas. Unfortunately, I need all my data for each subject to be in a single row in the VA table as I also need to be able to create percentages via the Aggregated Measures facility within VA. These percentages need to be sensitive to the amount of expand/contract or drill-down on the crosstab rows (Area/Region/Site/SubSite) that the user chooses.

I have experimented with transposing my data into one row per ID/Measure Type, which I think is what you are suggesting, and I fall over when I come to create the percentages inside VA. I really need the percentages, but I would also like the visual grouping of measures if only to allow the user to easily see that one set of measures (Languages) is somehow separate from the other (non-Languages).

I had thought that I might be able to shade the background of one set of measure columns differently to the other set, but VA 7.x does not allow me to amend the style of individual measure columns in the crosstab object, which seems quite a let down. I am beginning to think that you are correct that this cannot be done.

It seems amazing that Proc TABULATE in SAS 9.4 which has been around for many years, is so powerful although tricky to code (unless you use a SAS/Enterprise Guide task of course ), and can do what I need, and yet SAS/VA does not seem to be able to offer this. I am optimistic that someone out there in the SAS Community knows how this can be done.

Anyhow, thank you again for your suggestions.

Does anyone else have any ideas ?

Downunder Dave

Wellington

[FredrikE · Accepted solution]

If you want to change font or background you can create a display rule for each measure that never will be fulfilled and then change the color.

Try create a rule for one measure saying: IF [measure] <> -10000000000 then color background = 'blue' (you do this with point'n ckick :))

In this way it should be possible to set one color for each group.

//Fredrik

[DaveShea]

Hi Fredrik,

Wow ! That is a great idea about applying never-going-to-happen display rules to each of my measure columns, thank you very much for your help.

I have experimented with the idea and it is certainly a good half-way house towards what I am looking for.

I swapped the idea around a bit and made a rule that was an always-going-to-happen situation for my data, like this for the column showing the results for the French exam:

I then made a similar rule for each of the other columns that I wanted to highlight.

However, I am still on the look out for a full solution to this, whereby I can assign a label across the top of the Language and non-Language columns as in my Proc TABULATE report, so if anyone else has a bright idea, feel free to jump in.

Many thanks,

Downunder Dave

Wellington

[SASKiwi]

You can incorporate SAS Stored Processes in SAS VA, so it would be possible to run a  REAL PROC TABULATE or PROC REPORT and have the results populate a VA web page with spanning column headers. You could slice and dice using VA filters to produce the report you want. The downside would be the report object would no longer be dynamic but would be a static based on chosen filter selections.

[DaveShea]

Hi All,

Sorry for the delay in updating this post, I've been down in Fiordland, one of our fantastic national parks, blissfully out of touch.

Thank you very much for your help and ideas. As you will see, I have flagged Fredrik's second suggestion, using Display Rules to colour specific columns, as the solution to my original question. Ideally though, I would like the Crosstab object in Visual Analytics to behave a bit more like Proc TABULATE in terms of nesting of columns. For the moment. though, I have a solution that I can put forward to my colleagues and hopefully make them happy or at least happier.

Once I've unpacked my rucsac and cleaned my boots I aim to put an entry on the SAS Ballot page of Communities to try to lobby support to get the Crosstab object in SAS Visual Analytics improved somewhat.

Here's a picture of one of Fiordland's cheeky mountain parrots, the Kea.

Cheers,

Downunder Dave.

Wellington

[angeldbracho]

Hi Basl, 

I'm using SAS VisualAnalytics 7.4

I was trying to create a cross table with the same idea you wrote, I mean, creating customized categories. But unfortunately I have been having a problem since I can not use more than one categorie as files in order to organize the visualization like a vertical list. 

What I would like to create is a cross tab where I have a vertical list of several customized categories and a hierarchy inside of every category.

Do you know any way to get this visualization?

I really would appreciate any hel you could give me.

Regards!

PD: It should be like the image attached