Why cannot I get distinct number of subjects with this code?

[Mirisage]

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

[art297]

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;

[Mirisage]

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

[Linlin · Accepted solution]

proc sql;

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

quit;

[Howles]

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

[Mirisage]

Hi Ahmed, Linlin, Art and Howles,

Many thanks to all of you.

Regards

Mirisage

[AhmedAl_Attar]

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;