How to suppress data based on conditions

[xyg]

Hi,

I need to suppress data according to the following rules:

1. If any cell is less than 20, the cell is suppressed for the purpose of confidentiality.
2. When there is suppression of a single cell, the next smallest cell(s) will need to be suppressed so that the value of the suppressed cell cannot be calculated by subtracting the other values from the total for a region.

Here’s the sample data:

Region         Group          Count

Region1       Group 1          70

Region1       Group2           30

Region1       Group3          10 

Region1       Total                110 

Region2       Group 1          210

Region2       Group2           90

Region2       Group3           50 

Region2       Total                350 

Region3       Group 1          170

Region3       Group2           15

Region3       Group3           60 

Region3       Total                245 

Thank you!

[art297]

I think that the following will do what you want:

data have;
  input Region $ Group $&: Count;
  cards;
Region1 Group 1 70
Region1 Group2 30
Region1 Group3 10
Region1 Total 110
Region2 Group 1 210
Region2 Group2 90
Region2 Group3 50
Region2 Total 350
Region3 Group 1 170
Region3 Group2 15
Region3 Group3 60
Region3 Total 245
;

proc sort data=have out=want;
  by Region Count;
run;

data want (drop=delete last_delete);
  set want;
  by Region;
  if count lt 20 then delete=1;
  last_delete=ifn(first.Region,.,lag(delete));
  if last_delete then delete=1;
  if delete ne 1;
run;

HTH,

Art, CEO, AnalystFinder.com

[xyg]

Hi Art,

Thank you very much for your quick response! I tried the code and it's working well! The only thing is that I'd like a blank for suppressed data, instead of deleting them. I'd like the data like this:

Region Group Count

Region1 Group1 70

Region1 Group2

Region1 Group3

Region1 Total 110

Region2 Group1 210

Region2 Group2 90

Region2 Group3 50

Region2 Total 350

Region3 Group1 170

Region3 Group2

Region3 Group3

Region3 Total 245

Thank you!

[ballardw]

So instead of deletion create another variable and use that one in your display/report/ or what ever.

[xyg]

Thank you Art and Ballardw for you help! The code below solved my question!

data have;

input Region $ Group $ Count;

cards;

Region1 Group1 70

Region1 Group2 30

Region1 Group3 10

Region1 Total 110

Region2 Group1 210

Region2 Group2 90

Region2 Group3 50

Region2 Total 350

Region3 Group1 170

Region3 Group2 15

Region3 Group3 60

Region3 Total 245

;

run;

proc sort data=have out=want;

by Region Count;

run;

　

data want(drop=supp last_supp);;

set want;

by Region;

if count lt 20 then supp=1;

last_supp=ifn(first.Region,.,lag(supp));

if last_supp then supp=1;

if supp = 1 then count=.;

run;

proc sort;

by Region group;

run;

[art297]

data have;

  input Region $ Group $&: Count;
  cards;
Region1 Group 1 70
Region1 Group2 30
Region1 Group3 10
Region1 Total 110
Region2 Group 1 210
Region2 Group2 90
Region2 Group3 50
Region2 Total 350
Region3 Group 1 170
Region3 Group2 15
Region3 Group3 60
Region3 Total 245
;

proc sort data=have out=want;
  by Region Count;
run;

data want (drop=delete last_delete);
  set want;
  by Region;
  if count lt 20 then delete=1;
  last_delete=ifn(first.Region,.,lag(delete));
  if last_delete then delete=1;
  if delete eq 1 then call missing(count);
run;

HTH,

Art, CEO, AnalystFinder.com

[xyg]

Hi Art,

Thank you, thank you!!!

[xyg]

Hi,

I have a further question regarding the data suppression. I need to suppress data according to the following rules (a third rule is added):

1. If any cell is less than 20, the cell is suppressed for the purpose of confidentiality.
2. When there is suppression of a single cell, the next smallest cell(s) will need to be suppressed so that the value of the suppressed cell cannot be calculated by subtracting the other values from the total for a region.
3. If two or more of the cells within a region are less than 20 (will be suppressed), then the next smallest cell(s) will NOT be suppressed, as the values of suppressed cells cannot be determined.

The sample data is here:

Region1 Group1 70

Region1 Group2 30

Region1 Group3 10

Region1 Total 110

Region2 Group1 210

Region2 Group2 90

Region2 Group3 50

Region2 Total 350

Region3 Group1 170

Region3 Group2 15

Region3 Group3 60

Region3 Total 245

Region4 Group1 55

Region4 Group2 15

Region4 Group3 10

Region4 Total 80

And I want the data is like this:

Region1 Group1 70

Region1 Group2

Region1 Group3

Region1 Total 110

Region2 Group1 210

Region2 Group2 90

Region2 Group3 50

Region2 Total 350

Region3 Group1 170

Region3 Group2

Region3 Group3

Region3 Total 245

Region4 Group1 55

Region4 Group2

Region4 Group3

Region4 Total 80

Thank you!

[art297 · Accepted solution]

Just add a counter and include that in the criteria. e.g.:

proc sort data=have out=want;
  by Region Count;
run;

data want (drop=delete last_delete dcounter);
  set want;
  by Region;
  if first.Region then dcounter=0;
  if count lt 20 then do;
    delete=1;
    dcounter+1;
  end;
  last_delete=ifn(first.Region,.,lag(delete));
  if last_delete and dcounter lt 2 then delete=1;
  if delete eq 1 then call missing(count);
run;

HTH,

Art, CEO, AnalystFinder.com

[xyg]

Thank you again, Art! This is exactly what I want! Thank you for all your helps!