topic How to suppress data based on conditions in SAS Programming

How to suppress data based on conditions

xyg — Tue, 17 Jan 2017 17:20:17 GMT

Hi,

I need to suppress data according to the following rules:

If any cell is less than 20, the cell is suppressed for the purpose of confidentiality.
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!

Re: How to suppress data based on conditions

art297 — Tue, 17 Jan 2017 17:36:54 GMT

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

Re: How to suppress data based on conditions

xyg — Tue, 17 Jan 2017 18:11:10 GMT

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!

Re: How to suppress data based on conditions

ballardw — Tue, 17 Jan 2017 18:15:43 GMT

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

Re: How to suppress data based on conditions

art297 — Tue, 17 Jan 2017 18:38:24 GMT

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

Re: How to suppress data based on conditions

xyg — Tue, 17 Jan 2017 18:41:11 GMT

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;

Re: How to suppress data based on conditions

xyg — Tue, 17 Jan 2017 18:44:55 GMT

Hi Art,

Thank you, thank you!!!

Re: How to suppress data based on conditions

xyg — Wed, 18 Jan 2017 19:06:39 GMT

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

If any cell is less than 20, the cell is suppressed for the purpose of confidentiality.
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.
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!

Re: How to suppress data based on conditions

art297 — Wed, 18 Jan 2017 19:39:21 GMT

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

Re: How to suppress data based on conditions

xyg — Thu, 19 Jan 2017 13:16:35 GMT

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