Dynamic grouping?

[stanh]

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.

[PetriRoine]

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.

1. 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)
2. 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
3. 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
4. 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 )

5. 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 )

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

I hope this helps.

Best regards,

Petri

[stanh]

Thank you, @PetriRoine 

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

[PetriRoine]

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

[stanh]

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

Thank you!

[Sam_SAS]

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.

[stanh]

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

[Sam_SAS]

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

[stanh]

Thank you @Sam_SAS

[SASKiwi]

@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