BookmarkSubscribeRSS Feed
Calcite | Level 5



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:

ProjectSoftware 1Number of copiesSoftware 2Number of copiesSoftware 3Number of copies


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.

Pyrite | Level 9

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,


Calcite | Level 5

Thank you, @PetriRoine 

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

Pyrite | Level 9

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


Best regards,


Calcite | Level 5

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

Thank you!


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.

Calcite | Level 5
Thank you @Sam_SAS.
I am very new SAS. If you could point me to any documentation on this will be great.
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:

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.

Calcite | Level 5
Thank you @Sam_SAS
Opal | Level 21

@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



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 9 replies
  • 1 like
  • 4 in conversation