Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Super Contributor
Posts: 338
Accepted Solution

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

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

Attachment

Accepted Solutions
Solution
‎10-05-2012 12:44 PM
Super Contributor
Posts: 1,636

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

proc sql;

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

quit;

View solution in original post


All Replies
PROC Star
Posts: 7,471

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

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;

Super Contributor
Posts: 338

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

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
Super Contributor
Posts: 1,636

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

proc sql;

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

quit;

Regular Contributor
Posts: 184

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

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

Super Contributor
Posts: 338

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

Hi Ahmed, Linlin, Art and Howles,

Many thanks to all of you.

Regards

Mirisage

Regular Contributor
Posts: 216

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

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 292 views
  • 7 likes
  • 5 in conversation