CODEWITHSUNDEEP
sas
devon
devon

Reputation: 321

Delete the group that none of its observation contain the certain value in SAS

I want to delete the whole group that none of its observation has NUM=14

So something likes this: Original DATA

ID  NUM 
1  14
1  12
1  10
2  13
2  11
2  10
3  14
3  10

Since none of the ID=2 contain NUM=14, I delete group 2. And it should looks like this:

ID  NUM 
1  14
1  12
1  10
3  14
3  10

This is what I have so far, but it doesn't seem to work.

data originaldat;
set newdat;
by ID;
If first.ID then do;
        IF NUM EQ 14 then Score = 100;
        Else Score = 10;
    end;
else SCORE+1;
run; 

data newdat;
set newdat;
   If score LT 50 then delete;
run;

Upvotes: 3

Views: 1216

Answers (3)

Harshad Patil
Harshad Patil

Reputation: 313

data in;
input id num;
cards;
1 14
1 12
1 10
2 16
2 13
3 14
3 67
;

/* To find out the list of groups which contains num=14, use below SQL */

proc sql;
  select distinct id into :lst separated by ','
  from in
  where num = 14;
quit;

/* If you want to create a new data set with only groups containing num=14 then use following data step */

data out;
 set in;
 where id in (&lst.);
run;

Upvotes: 1

SRSwift
SRSwift

Reputation: 1710

An approach using proc sql would be:

proc sql;
    create table newdat as
    select * 
    from originaldat
    where ID in (
        select ID 
        from originaldat
        where NUM = 14
    );
quit;

The sub query selects the IDs for groups that contain an observation where NUM = 14. The where clause then limits the selected data to only these groups.

The equivalent data step approach would be:

/* Get all the groups that contain an observation where N = 14 */
data keepGroups;
    set originaldat;
    if NUM = 14;
    keep ID;
run;
/* Sort both data sets to ensure the data step merge works as expected */
proc sort data = originaldat;
    by ID;
run;
/* Make sure there are no duplicates values in the groups to be kept */
proc sort data = keepGroups nodupkey;
    by ID;
run;
/* 
    Merge the original data with the groups to keep and only keep records
    where an observation exists in the groups to keep dataset
*/
data newdat;
    merge 
        originaldat 
        keepGroups (in = k);
    by ID;
    if k;
run;

In both datasets the subsetting if statement is used to only output observations when the condition is met. In the second case k is a temporary variable with value 1(true) when a value is read from keepGroups an 0(false) otherwise.

Upvotes: 4

Joe
Joe

Reputation: 63424

You're sort of getting at a DoW loop here, but not quite doing it right. The problem (Assuming the DATA/SET names are mistyped and not actually wrong in your program) is the first data step doesn't append that 100 to every row - only to the 14 row. What you need is one 'line' per ID value with a keep/no keep decision.

You can either do this by doing your first data step, but RETAIN score, and only output one row per ID. Your code would actually work, based on 14 being the first row, if you just fixed your data/set typo; but it only works when 14 is the first row.

data originaldat;   
input ID  NUM ;
datalines;
1  14
1  12
1  10
2  13
2  11
2  10
3  14
3  10
;;;;
run;

data has_fourteen;
set originaldat;
by ID;
retain keep;
If first.ID then keep=0;
if num=14 then keep=1;
if last.id then output;
run; 

data newdata;
  merge originaldat has_fourteen;
  by id;
  if keep=1;
run;

That works by merging the value from a 1-per-ID to the whole dataset.

A double DoW also works.

data newdata;
  keep=0;
  do _n_=1 by 1 until (last.id);
    set originaldat;
    by id;
    if num=14 then keep=1;
  end;
  do _n_=1 by 1 until (last.id);
    set originaldat;
    by id;
    if keep=1 then output;
  end;
run;

This works because it iterates over the dataset twice; for each ID, it iterates once through all records, looking for a 14, if it finds one then setting keep to 1. Then it reads all records again for that ID, and keeps if keep=1. Then it goes on to the next set of records by ID.

Upvotes: 2

Related Questions