BookmarkSubscribeRSS Feed
shiv999
Calcite | Level 5
Hi Team,
Can you please help me in colour the duplicate values using sas. Please see the attachment if index I'd is duplicate then code should compare the row and colour the non duplicate columns in duplicate rows.
2 REPLIES 2
shiv999
Calcite | Level 5
Please see the output file here I am looking something like this . Please help
PetriRoine
Pyrite | Level 9

Hello @shiv999 ,

 

I played a little with my VA 8.5 and got this as a results:

Capture.PNG

If this fits your needs fully or partially then here's how I did it. I think this should be doable also with VA8.3 and newer versions.

 

  1. To find the duplicate rows we will create a new data aggregation
    In the Data pane, select a data source SAS_SAMPLE. Click 
     

    the icon on right looking like database and then select New data from aggregation of data-source. The New Aggregated Data window is displayed. Edit it to look the same as below. The reasoning here is that we should get Frequence=1 when there is only one row with specific Index_id and tax. If the pair exist more than once then Frequency>1. We will use this in the List table to spot the duplicate rows.
    Capture3.PNG

  2. Now add the results from step before to original data
    Create a custom data source join to add the Frequency column to SAS_SAMPLE. In the Data pane, select a data source SAS_SAMPLE_AGGREGATED. Click the icon on right looking like database and then select New data from join with SAS_SAMPLE_AGGREGATED. Note that VA will create the Join with default settings first and you need to edit it after creation to get what we need:


    Capture2.PNG

  3. Finally we create rules to highlight cells in the list table
    My List table roles are as below. The variable Aggregated Frequency I have used in Hidden role is the one we created in the first step and added to SAS_SAMPLE in the data join step.
    Capture5.PNG

    Use display rules to highlight cells:
    Capture4.PNG

Let me know if you have any questions.

 

Best regards,

Petri

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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
  • 2 replies
  • 879 views
  • 0 likes
  • 2 in conversation