Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Why cannot I get distinct number of subjects with ...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-05-2012 10:46 AM

Hi SAS Forum,

In the attached data set, there are 7 distinct account numbers.

They are:

Bank_number Account_number

10 111

10 777

40 333

40 555

40 666

70 111

70 222

Question:

This code supposed to provide me the distinct numbr of accounts is returning 6 as the number of distinct account.

**proc** **sql**;

select count(distinct account_number)

from a.book1;

**quit**;

Could anyone help me to understand why ?

Thank you

Mirisage

Accepted Solutions

Solution

10-05-2012
12:44 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-05-2012 12:44 PM

proc sql;

select count(*) as total from (select distinct bank_number,account_number from a);

quit;

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-05-2012 11:08 AM

I presume that you would also want to see the account numbers. How about something like?:

proc sql;

select account_number, count(account_number)

from a.book1

group by account_number

;

quit;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-05-2012 12:22 PM

**Hi Art,**

**Thank you.**

**Sorry my question was not clear. Let me to simplyfy my question. **

**In this data set, I actually have 7 distinct account_numbers as I have shown in green color. **

**data** A;

input bank_number account_number $ **4**-**6**;

cards;

10 111

10 111

70 222

70 222

40 333

70 111

40 555

40 666

10 777

;

**run**;

Bank_number Account_number

10 111

10 777

40 333

40 555

40 666

70 111

70 222

**Question:**

**When I have run below code, it says there are only 6 distinct accounts (see the output inside the square below).**

**proc** **sql**;

select count(distinct account_number)

from a;

**quit**;

output

6 |

But acutally there are 7 distinct accounts.

When I have run your code belwo also it says there are only 6 disticnt accounts whereas it should have siad there are 7.

**proc** **sql**;

select account_number, count(account_number)

from a

group by account_number

;

**quit**;

This is the output of above code

111 | 3 |

222 | 2 |

333 | 1 |

555 | 1 |

666 | 1 |

777 | 1 |

Question:

So, how could I get the correct number which should be 7.

Thanks

Miris

Solution

10-05-2012
12:44 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-05-2012 12:44 PM

proc sql;

select count(*) as total from (select distinct bank_number,account_number from a);

quit;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-05-2012 01:48 PM

select count( distinct cat(bank_number , account_number) ) from a ;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-07-2012 09:52 PM

Hi Ahmed, Linlin, Art and Howles,

Many thanks to all of you.

Regards

Mirisage

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-05-2012 12:49 PM

Try

Proc sql;

Select Distinct bank_number,account_number

from a

;

quit;

if you want the physical count, then try this

proc sql;

select count(*)

from (Select Distinct bank_number,account_number from a)

;

quit;