topic Re: Dynamic grouping? in SAS Visual Analytics

Dynamic grouping?

stanh — Wed, 26 Aug 2020 08:19:54 GMT

Hi,

I am not sure if the title is accurate. Here is what I am trying to achieve and hope some of you can help.

I have my source loaded from an excel file with the following data:

Project	Software 1	Number of copies	Software 2	Number of copies	Software 3	Number of copies
A	Excel	2	Word	1	Powerpoint	1
B	Word	1	Photoshop	2	Visio	1
C	Powerpoint	3	Excel	1

I would like to be able to show the above data using a pie chart to show the number of copies.

The software name is dynamic.

Hope the problem is clear.

Thank you very much for your help.

Re: Dynamic grouping?

PetriRoine — Wed, 26 Aug 2020 13:01:37 GMT

Hello @stanh

This is doable directly in VA designer, but the easiest way would be manipulate the data structure to better support vizualisation.

If you want to do it with this data structure here's the steps that I did.

Use New aggregated data source... to create three new data sources: SW1, SW2, SW3
Selected items are Software n and Number of copies n (where n is 1, 2, 3)
Use New data source join... to join SW1 and SW2 -> SW1SW2
Join type: Full Join.
Join conditions: Software 1 = Software 2
Choose columns: Software 1, Number of copies 1, Software 2, Number of copies 2
Use New data source join... again to join SW1SW2 and SW3
Join type: Full Join.
Join conditions: Software 1_2 = Software 3
Choose columns: Software 1, Number of copies 1, Software 2, Number of copies 2, Software 3, Number of copies 3
Create a New calculated item called My SW
Notice that I've renamed columns f.ex. Software 1 -> SW1. I have probably done some other renamings along the way, but don't remember all.
```
IF ( 'SW1'n NotMissing )
RETURN 'SW1'n
ELSE (
IF ( 'SW2'n NotMissing )
RETURN 'SW2'n
ELSE 'SW3'n )
```

Create a New calculated item called My Copies

(
IF ( 'Number of copies 1'n NotMissing )
RETURN 'Number of copies 1'n
ELSE 0 ) + (
IF ( 'Number of copies 2'n NotMissing )
RETURN 'Number of copies 2'n
ELSE 0 ) + (
IF ( 'Number of copies 3'n NotMissing )
RETURN 'Number of copies 3'n
ELSE 0 )

Then create a Pie Chart using category My SW and measure My Copies.

I hope this helps.

Best regards,

Petri

Re: Dynamic grouping?

stanh — Wed, 26 Aug 2020 13:26:43 GMT

Thank you, @PetriRoine

Just wondering if what you have suggested is doable in VA 7.3?

Re: Dynamic grouping?

PetriRoine — Wed, 26 Aug 2020 13:53:18 GMT

Sorry, I don't think it's possible in VA 7.3 as aggregated data source feature came in VA 8.3.

Best regards,

Petri

Re: Dynamic grouping?

stanh — Wed, 26 Aug 2020 13:56:10 GMT

Do you have any suggestion how I can do it in VA 7.3?

Thank you!

Re: Dynamic grouping?

Sam_SAS — Wed, 26 Aug 2020 14:41:22 GMT

It should be possible to transform your data structure by using SAS DATA step or SQL syntax. Preparing the data in this way should greatly simplify visualization in VA.

Re: Dynamic grouping?

stanh — Sun, 30 Aug 2020 23:20:47 GMT

Thank you @Sam_SAS.
I am very new SAS. If you could point me to any documentation on this will be great.

Re: Dynamic grouping?

Sam_SAS — Sun, 30 Aug 2020 23:48:56 GMT

Hi Stanh,

I am not a great expert on Base SAS programming, so I would recommend that you create a thread in the SAS Programming community here:
https://communities.sas.com/t5/SAS-Programming/bd-p/programming

If you show them the column structure you have in Excel, they should be able to help you come up with the SAS code to read it in properly for VA.

Sam

Re: Dynamic grouping?

stanh — Mon, 31 Aug 2020 01:43:20 GMT

Thank you @Sam_SAS

Re: Dynamic grouping?

SASKiwi — Wed, 02 Sep 2020 04:11:55 GMT

@stanh - Changing your data from wide to long format should do the trick:

Project	Software	Copies
A	Excel	2
A	Word	1
A	Powerpoint	1
B	Word	1
B	Photoshop	2
B	Visio	1
C	Powerpoint	3
C	Excel	1