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
proc sql;
select count(*) as total from (select distinct bank_number,account_number from a);
quit;
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;
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
proc sql;
select count(*) as total from (select distinct bank_number,account_number from a);
quit;
select count( distinct cat(bank_number , account_number) ) from a ;
Hi Ahmed, Linlin, Art and Howles,
Many thanks to all of you.
Regards
Mirisage
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;
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.
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.