To select a value with filter condition

[sathasivam]

Hi Everyone,

Am new to SAS and am looking for a solution for a problem

I have a dataset with two columns, 

Account Number            Country

123                                 Australia

123                                Sweden

123                                Denmark

456                                Australia 

678                                Gambia

987                                Combodia

So the problem is, I have to select the account number which is present only in "Australia" not in other countries. So the expected output from above dataset is 456 Australia. We should not select 123 because it is present in other countries as well.

Thanks in advance for your help

[ballardw]

Are any Account Number and Country combinations repeated? If so you should provide an example with that behavior and show the desired result.

[sathasivam]

Hello Ballard, 

Thanks for your response. No repetition of account numbers and countries

Account number may have multiple counties

[Reeza]

Use SQL.

proc sql;
create table want as
select distinct acctNo
from have 
group by acctNo
having max(country)=min(country) = 'Australia';
quit;

In the future please post data as a data step following the instructions here:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

---

@sathasivam wrote:

Hi Everyone,

Am new to SAS and am looking for a solution for a problem

I have a dataset with two columns, 

Account Number            Country

123                                 Australia

123                                Sweden

123                                Denmark

456                                Australia 

678                                Gambia

987                                Combodia

So the problem is, I have to select the account number which is present only in "Australia" not in other countries. So the expected output from above dataset is 456 Australia. We should not select 123 because it is present in other countries as well.

Thanks in advance for your help

---

[sathasivam · Accepted solution]

Hi Reeza,

If I use the code which you have given, it is returning account numbers 123 and 456. But the problem is, it should return only 456 because account number 123 is present in other countries also. We need to pickup account number present only in "Australia" and not in other countries 

Thank you

[Reeza]

Show an example of how that happens please, I tested it without issue.

That will happen if you group by account number AND country but this is grouped just by account number.

[ballardw]

Pleas when discussing a proposed solution show from the log the code and any notes from SAS.

Sometimes we use generic bits, such as data set names, because you haven't provided yours completely. Then your code that you execute may include some of these bits. Which may result in an error and not replace a previously created data set from when you were attempting your solutions. So we need to know what happens when your code gets different results.

You might be surprised how many people will type code from a proposed solution and completely rewrite it missing key syntax elements.