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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.