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

Solved
Super Contributor
Posts: 312

# 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
‎05-02-2017 04:55 AM
SAS Super FREQ
Posts: 830

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

Posted in reply to Scott_Mitchell

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

All Replies
SAS Super FREQ
Posts: 830

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

[ Edited ]
Posted in reply to Scott_Mitchell

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

Super Contributor
Posts: 312

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

Posted in reply to Bruno_SAS

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
‎05-02-2017 04:55 AM
SAS Super FREQ
Posts: 830

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

Posted in reply to Scott_Mitchell

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

Frequent Contributor
Posts: 131

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

[ Edited ]
Posted in reply to Bruno_SAS

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: 312

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

Posted in reply to Bruno_SAS

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

I appreciate your help.

Super Contributor
Posts: 312

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

Posted in reply to 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.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
• 6 replies
• 389 views
• 0 likes
• 3 in conversation