BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mirisage
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

proc sql;

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

quit;

View solution in original post

6 REPLIES 6
art297
Opal | Level 21

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
Obsidian | Level 7

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
Lapis Lazuli | Level 10

proc sql;

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

quit;

Howles
Quartz | Level 8

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

Mirisage
Obsidian | Level 7

Hi Ahmed, Linlin, Art and Howles,

Many thanks to all of you.

Regards

Mirisage

AhmedAl_Attar
Rhodochrosite | Level 12

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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