topic How to extract id number based on another variable? in SAS Programming

How to extract id number based on another variable?

zihdonv19 — Mon, 07 Aug 2023 16:20:54 GMT

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

Re: How to extract id number based on another variable?

PaigeMiller — Mon, 07 Aug 2023 16:28:40 GMT

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.

Re: How to extract id number based on another variable?

zihdonv19 — Mon, 07 Aug 2023 16:36:04 GMT

Hi!

Thanks for your reply.

The output I'd like to have is:

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.

Re: How to extract id number based on another variable?

Tom — Mon, 07 Aug 2023 18:33:20 GMT

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;

Re: How to extract id number based on another variable?

zihdonv19 — Mon, 07 Aug 2023 20:08:09 GMT

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"?

Re: How to extract id number based on another variable?

Tom — Mon, 07 Aug 2023 23:25:11 GMT

@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.