topic Re: To select a value with filter condition in SAS Programming

To select a value with filter condition

sathasivam — Thu, 20 Jan 2022 20:35:27 GMT

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

Re: To select a value with filter condition

ballardw — Thu, 20 Jan 2022 20:52:06 GMT

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

Re: To select a value with filter condition

Reeza — Thu, 20 Jan 2022 20:54:10 GMT

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-data-AKA-generate/ta-p/258712

@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

Re: To select a value with filter condition

sathasivam — Thu, 20 Jan 2022 20:55:52 GMT

Hello Ballard,

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

Account number may have multiple counties

Re: To select a value with filter condition

sathasivam — Thu, 20 Jan 2022 21:01:23 GMT

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

Re: To select a value with filter condition

Reeza — Thu, 20 Jan 2022 21:48:03 GMT

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.

Re: To select a value with filter condition

ballardw — Thu, 20 Jan 2022 21:55:00 GMT

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.