topic Re: Find when first row in a group doesn't match other rows in SAS Programming

Find when first row in a group doesn't match other rows

jk2018 — Wed, 15 Sep 2021 16:33:20 GMT

I need to find when the first row within a group, grouping by case_id, has a value of unknown for the variable country but any of the other rows within a group have a known value. Below is an example of the data that I would be starting with.

Case_id	Country
12456789	unknown
12456789	Mexico
12456789	unknown
56789	unknown
56789	Cuba
56789	USA

output I would want:

Case_id	Country
12456789	Mexico
56789	Cuba
56789	USA

Re: Find when first row in a group doesn't match other rows

Reeza — Wed, 15 Sep 2021 16:36:48 GMT

That seems to just filter out all unknowns.

Is there a case where you'd keep a different record?

data want;
set have;
where country ne 'unknown';
run;

@jk2018 wrote:

I need to find when the first row within a group, grouping by case_id, has a value of unknown for the variable country but any of the other rows within a group have a known value. Below is an example of the data that I would be starting with.

Case_id

Country

12456789

unknown

12456789

Mexico

12456789

unknown

56789

unknown

56789

Cuba

56789

USA

output I would want:

Case_id

Country

12456789

Mexico

56789

Cuba

56789

USA

Re: Find when first row in a group doesn't match other rows

jk2018 — Wed, 15 Sep 2021 18:19:36 GMT

If the first observation for each case_id does not equal unknown then I need all the observations for that case_id to be excluded from the output. For example, if a record like this was in the dataset I would not want it in the final dataset:

Case_id	Country
777	Mexico
777	Mexico
777	unknown

Re: Find when first row in a group doesn't match other rows

novinosrin — Wed, 15 Sep 2021 18:52:00 GMT



data have;
input (Case_id	Country) (:$15.);
cards;
12456789	unknown
12456789	Mexico
12456789	unknown
56789	unknown
56789	Cuba
56789	USA
777 Mexico
777 Mexico
777 unknown
;


data want;
 set have;
 by case_id;
 retain t;
 if first.case_id then if country='unknown' then t=1;
 else t=.;
 if t and  country ne 'unknown';
 drop t;
run;

Re: Find when first row in a group doesn't match other rows

Tom — Wed, 15 Sep 2021 19:31:05 GMT

Delete the unknown observations when they are not the only observation.

data have;
  input Case_id :$10. Country :$20.;
cards;
12456789 unknown
12456789 Mexico
12456789 unknown
56789 unknown
56789 Cuba
56789 USA
9999 unknown
;

data want ;
  set have;
  by case_id ;
  if country='unknown' and not (first.case_id and last.case_id) then delete;
run;

Obs    Case_id     Country

 1     12456789    Mexico
 2     56789       Cuba
 3     56789       USA
 4     9999        unknown