Exploring, modeling, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

Adding "N/A" Text to All Columns in a Cross Tab Where the Row Total is Less Than X.

Accepted Solution Solved
Reply
Super Contributor
Posts: 296
Accepted Solution

Adding "N/A" Text to All Columns in a Cross Tab Where the Row Total is Less Than X.

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


Accepted Solutions
Solution
3 weeks ago
SAS Super FREQ
Posts: 619

Re: Adding "N/A" Text to All Columns in a Cross Tab Where the Row Total is Less Than X.

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


All Replies
SAS Super FREQ
Posts: 619

Re: Adding "N/A" Text to All Columns in a Cross Tab Where the Row Total is Less Than X.

[ Edited ]

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

Super Contributor
Posts: 296

Re: Adding "N/A" Text to All Columns in a Cross Tab Where the Row Total is Less Than X.

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

Solution
3 weeks ago
SAS Super FREQ
Posts: 619

Re: Adding "N/A" Text to All Columns in a Cross Tab Where the Row Total is Less Than X.

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

Frequent Contributor
Posts: 75

Re: Adding "N/A" Text to All Columns in a Cross Tab Where the Row Total is Less Than X.

[ Edited ]

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.

Super Contributor
Posts: 296

Re: Adding "N/A" Text to All Columns in a Cross Tab Where the Row Total is Less Than X.

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

 

I appreciate your help.

Super Contributor
Posts: 296

Re: Adding "N/A" Text to All Columns in a Cross Tab Where the Row Total is Less Than X.

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.
Post a Question
Discussion Stats
  • 6 replies
  • 146 views
  • 0 likes
  • 3 in conversation