BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

Hi All,

 

First time posting in the VA section after being a keen user of SAS for 12+ years.  So, thank you for your patience.

 

I have a request to obscure all measures (up to 3 groups) where the Row Total is less than X units.  It isn't suitable to remove this line from the data, the requestor has a requirement to apply "N/A" or Missing to these values?

 

Does anyone have thoughts on how I could achieve this?

 

Example:

 

Input

 

UNIT      MALE     FEMALE    TOTAL

1                  27              23           50

2                   2                 1             3

3                 45               10           55

 

Desired Output

 

UNIT      MALE     FEMALE    TOTAL

1                  27              23           50

2                N\A             N\A         N\A

3                 45               10           55

 

 

Thank you for your help.

 

Regards,

Scott

1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

Scott

 

If your final table has only a few columns like Female and Male, you can do the following:

 

Create a new column that only has values if the gender is Female, see here an example with sashelp.cars

IF ( 'DriveTrain'n = 'All' )
RETURN 'Invoice'n
ELSE .

You have to build a new data item for each value of your category

 

then build the total data item like so, since I returned missing in the previous data item I need to check for missing.

( 
  IF ( 'invoice_d_All'n NotMissing )
  RETURN 'invoice_d_All'n
  ELSE 0 ) + ( 
  IF ( 'invoice_d_Front'n NotMissing )
  RETURN 'invoice_d_Front'n
  ELSE 0 ) + ( 
  IF ( 'invoice_d_Rear'n NotMissing )
  RETURN 'invoice_d_Rear'n
  ELSE 0 )

then build your aggreated date items like so:

IF ( Sum [_ByGroup_] ('invoice_d_total'n) < 400000000 )
RETURN .
ELSE Sum [_ByGroup_] ('invoice_d_All'n)

The end result will look like this, the data items ending in _aggr are the new aggreated measures.

Snap1.png

 

 

Takes some effort, but for the case you have shown, this should work.

 

Bruno

View solution in original post

6 REPLIES 6
BrunoMueller
SAS Super FREQ

Hi

 

Edit: just realized that you want to test the total value, well that is different, but using similar technique might do the job.

 

To replace some values with missing, I suggest to create an aggregated measure, like this:

Snap1.png

 

This will return missing for values you do not want to display.

Snap2.png

 

To have "N/A", I do not know a way right now.

 

Bruno

Scott_Mitchell
Quartz | Level 8

Hi Bruno,

 

Thank you so much for your suggestion.  

 

Unfortunately, I have been attempting to adapt this logic for my specific use case (i.e. Total < X  then missing) for 8 hrs without success.  Do you have any other ideas that might be useful for resolving this or a starting point to work from?

 

On the same topic, but using a different approach, I attempted to utilise the Display Rules tab to format the row count for each column in white (the requestor doesn't seem overly bothered that these results will still be in included in the Grand Total). As you would expect this method failed also.

 

Thank you again for all your help.

 

Regards,

Scott

BrunoMueller
SAS Super FREQ

Scott

 

If your final table has only a few columns like Female and Male, you can do the following:

 

Create a new column that only has values if the gender is Female, see here an example with sashelp.cars

IF ( 'DriveTrain'n = 'All' )
RETURN 'Invoice'n
ELSE .

You have to build a new data item for each value of your category

 

then build the total data item like so, since I returned missing in the previous data item I need to check for missing.

( 
  IF ( 'invoice_d_All'n NotMissing )
  RETURN 'invoice_d_All'n
  ELSE 0 ) + ( 
  IF ( 'invoice_d_Front'n NotMissing )
  RETURN 'invoice_d_Front'n
  ELSE 0 ) + ( 
  IF ( 'invoice_d_Rear'n NotMissing )
  RETURN 'invoice_d_Rear'n
  ELSE 0 )

then build your aggreated date items like so:

IF ( Sum [_ByGroup_] ('invoice_d_total'n) < 400000000 )
RETURN .
ELSE Sum [_ByGroup_] ('invoice_d_All'n)

The end result will look like this, the data items ending in _aggr are the new aggreated measures.

Snap1.png

 

 

Takes some effort, but for the case you have shown, this should work.

 

Bruno

itchyeyeballs
Pyrite | Level 9

This may be of no use what so ever but an alternative option we have used to hide results which are less than a certain number and protect anonymity is to round to the nearest x (in the example below 5)

 

( ( Sum [_ByGroup_] ('Data_Item'n) / 5 ) Round 1 ) * 5

This approach is quick and simple to apply accross an entire report and stops users from reverse engineering column totals to work out hidden values.

 

i.e. you dont have to worry about

 

UNIT      MALE     FEMALE    TOTAL

1                  27              23           50

2                5              N\A             6

3                 45               10           55

 

as you would get 

 

UNIT      MALE     FEMALE    TOTAL

1                  30              20           50

2                5            0        5

3                 45               10           55

 

The obvious downside is your users would have to put up with less accurate figures and get used to some of parts not adding up to total.

Scott_Mitchell
Quartz | Level 8

Thank you Bruno.   That put me on the right track.

 

I appreciate your help.

Scott_Mitchell
Quartz | Level 8
Thanks Itchy. Accuracy is paramount in this situation, but I appreciate you taking the time to respond. Could definitely be a solution for a future issue however.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 6 replies
  • 1329 views
  • 0 likes
  • 3 in conversation