topic Re: Why cannot I get distinct number of subjects with this code? in SAS Procedures

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

Mirisage — Fri, 05 Oct 2012 14:46:10 GMT

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

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

art297 — Fri, 05 Oct 2012 15:08:24 GMT

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;

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

Mirisage — Fri, 05 Oct 2012 16:22:51 GMT

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

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

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

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

Linlin — Fri, 05 Oct 2012 16:44:57 GMT

proc sql;

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

quit;

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

AhmedAl_Attar — Fri, 05 Oct 2012 16:49:10 GMT

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;

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

Howles — Fri, 05 Oct 2012 17:48:16 GMT

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

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

Mirisage — Mon, 08 Oct 2012 01:52:37 GMT

Hi Ahmed, Linlin, Art and Howles,

Many thanks to all of you.

Regards

Mirisage