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

sas proc-sql
Joe Laert picture Joe Laert · Jun 26, 2015 · Viewed 10.8k times · Source

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.

Answer

Joe picture Joe · Jun 26, 2015

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;

Related questions

Limiting results in PROC SQL

I am trying to use PROC SQL to query a DB2 table with hundreds of millions of records. During the development stage, I want to run my query on an arbitrarily small subset of those records (say, 1000). I've tried using …

Read More
How to convert date in SAS to YYYYMMDD number format

In test_1 table, the my_date field is a "DATE9." format. I would like to convert it to a pure numeric format (number length 8) which is of the form YYYYMMDD. I would also like to do this in a proc …

Read More
convert datetime to date in proc-sql sas

I'm trying to convert datetime22.3 variable to ddmmmyy10. in proc sql, this is giving me ****** in the output column. How can I get the correct values in the output column?

Read More