topic Re: use sql distinct count a*b in SAS Programming

use sql distinct count a*b

walterwang — Tue, 29 Sep 2020 23:32:02 GMT

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?

Re: use sql distinct count a*b

jimbarbour — Tue, 29 Sep 2020 23:40:38 GMT

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

Re: use sql distinct count a*b

CurtisMackWSIPP — Tue, 29 Sep 2020 23:43:57 GMT

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

Re: use sql distinct count a*b

mkeintz — Wed, 30 Sep 2020 00:00:57 GMT

Another option:

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