use sql distinct count a*b

[walterwang]

I know if i want to find how many distinct a, I can use count (distinct a)

Now I need find how many different combination of a and b.

What I should do?

[jimbarbour]

If you create two temporary tables consisting of one with just A and the other with Just B, then join them to get the Cartesian product (no where clause or INNER JOIN) and then do a SELECT COUNT DISTINCT on the Cartesian product, I think that should give you the number you want.

Some thing like this but check my syntax:

PROC SQL;

  CREATE TABLE A_TAB as SELECT DISTINCT A FROM Source_Table;

  CREATE TABLE B_TAB as SELECT DISTINCT B FROM Source_Table;

  CREATE TABLE A_B_TAB as SELECT * FROM A_TAB, B_TAB;

  SELECT COUNT(DISTINCT *) FROM A_B_TAB;

QUIT;

Jim

[CurtisMackWSIPP]

 select count(*) from (select distinct a, b from HAVE) ;

[mkeintz · Accepted solution]

Another option:

  select count(distinct catx('.',sex,age) ) as n_sex_by_age from have ;