using count along with select in PROC SQL

[nirmalraj]

Hi,

Is there a possible way of performing a count when doing a PROC SQL.

An example of what I am trying to calculate:


/*
proc sql;

connect to db2 ;
create table mylib.a as
select * from connection to db2
( SELECT

abc as a
, def as b
, count(distinct abc) as a_count


from abcdef
where x = y ) ;

*/

this gives me an error.
An unexpected token "AS" was found following "STINCT abc)". Expected tokens may include: ")". SQLSTATE=42601

Do I need to specify something. I have not come across this kind of error before.

Thanks,
Raj

 

[ChrisW75]

AS is not generally part of ANSI standard SQL. It works in SAS SQL, but the bit in the brackets after "connection to DB2" gets sent directly to the RDMS. Remove the AS and it should work fine.

The count syntax looks OK, but you may need to do some grouping, give it a try and see if you get any errors, then sense check the data to make sure your counts are correct.