How to extract id number based on another variable?

[zihdonv19]

Hi!

I have a dataset like the one below.

I wish to extra the id where the variable "atc" only has "A10A".

So, in this example, I would extract id=282 and id=299.

Does anyone know how to achieve this in SAS? Thank you!!

id	atc
233	A10A
233	A10A
233	C05
233	V90A
233	V90A
233	V90A
233	V90A
233	G560
233	G561
233	G562
282	A10A
282	A10A
282	A10A
282	A10A
299	A10A

[PaigeMiller]

It would be extremely helpful, in this problem and all future problems, if you SHOW US the desired output.

Since you haven't done that, please do so now. Please also explain why rows 1 and 2, where atc="A10A" are not in the output.

[zihdonv19]

Hi!

Thanks for your reply.

The output I'd like to have is:

id

282

299

Rows 1 and 2 should not be in the output, because the atc value for id "233" is not restricted to "A10A". For example, id 233 also has atc value of "C05", "V90A" etc.

[Tom · Accepted solution]

Your data seems sorted, so let's use that to our advantage.

data want;
  set have;
  by id ;
  if first.id then other=0;
  retain other;
  if atc ne "A10A" then other=1;
  if last.id and not other;
run;

[zihdonv19]

Thanks for you reply Tom. Your solution works! 

May I further ask does "if last.lopnr and not other" always do the same as "if last.lopnr and other=0"?

[Tom]

---

@zihdonv19 wrote:

Thanks for you reply Tom. Your solution works! 

May I further ask does "if last.lopnr and not other" always do the same as "if last.lopnr and other=0"?

---

In this case yes.  But in the general case it is more like

if last.lopnr and (other=0 or missing(other));

SAS will evaluate a zero or missing value as FALSE and any other value as TRUE.