☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Obsidian | Level 7

## How to extract id number based on another variable?

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
1 ACCEPTED SOLUTION

Accepted Solutions
Super User

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

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;``````
5 REPLIES 5
Diamond | Level 26

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

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.

--
Paige Miller
Obsidian | Level 7

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

Hi!

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.

Super User

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

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;``````
Obsidian | Level 7

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

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

Super User

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

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

Discussion stats
• 5 replies
• 198 views
• 0 likes
• 3 in conversation