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

[Scott_Mitchell]

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

[BrunoMueller]

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:

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

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

Bruno

[Scott_Mitchell]

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 · Accepted solution]

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.

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

Bruno

[itchyeyeballs]

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]

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

I appreciate your help.

[Scott_Mitchell]

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.