CODEWITHSUNDEEP
sasproc-sql
Joe Laert
Joe Laert

Reputation: 136

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

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.

Upvotes: 4

Views: 15727

Answers (2)

Haikuo Bian
Haikuo Bian

Reputation: 906

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;

Upvotes: 2

Joe
Joe

Reputation: 63424

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;

Upvotes: 10

Related Questions