How to count distinct of the concatenation/cross of two variables in SAS Proc Sql?

Question

I know in teradata or other sql platforms you can find the count distinct of a combination of variables by doing:

select count(distinct x1||x2) from db.table

And this will give all the unique combinations of x1,x2 pairs.

This syntax, however, does not work in proc sql.

Is there anyway to perform such a count in proc sql?

Thanks.

Right, but I don't want the count of a sing variable; I want the count of the distinct number of combination of two (or more) variables. — Joe Laert, Jun 26 '15 at 16:21

score 10 · Answer 1 · answered Jun 26 '15 at 16:30

10

That syntax works perfectly fine in PROC SQL.

proc sql;
  select count(distinct name||sex)
    from sashelp.class;
quit;

If the fields are numeric, you must put them to character (using put) or use cat or one of its siblings, which happily take either numeric or character.

proc sql;
  select count(distinct cats(age,sex))
    from sashelp.class;
quit;

answered Jun 26 '15 at 16:30

Joe

62,789
6
49
67

1

Thank you!. Both my field were numeric, so I had to use cats(x1,x2) and it worked – Joe Laert Jun 26 '15 at 19:00

score 2 · Answer 2 · answered Jun 26 '15 at 17:27

2

This maybe redundant, but when you mentioned "combination", it instantly triggered 'permutation' in my mind. So here is one solution to differentiate these two:

DATA TEST;
    INPUT (X1 X2) (:$8.);
    CARDS;
A B
B A
C D
C D
;

PROC SQL;
    SELECT COUNT(*) AS TOTAL, COUNT(DISTINCT CATS(X1,X2)) AS PERMUTATION, 
        COUNT(DISTINCT CATS(IFC(X1<=X2,X1,X2),IFC(X1>X2,X1,X2))) AS  COMBINATION
    FROM TEST;
QUIT;

answered Jun 26 '15 at 17:27

Haikuo Bian

906
6
7

This looks like it would achieve what I want, too. Thanks. – Joe Laert Jun 26 '15 at 19:01

How to count distinct of the concatenation/cross of two variables in SAS Proc Sql?

2 Answers2