Where is not working

[bhca60]

23         proc sql;
24         create table d
25         as select
26         name,
27         dob,
28         count (distinct id) as idct
29         from database
30         where calculated idct = "2"
31         group by name, dob;
ERROR: The following columns were not found as CALCULATED references in the immediate query: idct.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
32         quit;

I've tried with and without "calculated" and with and without quotes around the number; I'm just trying to get those with more than 1 as an idct so as to get those with duplicate data.

[Reeza]

• Aggregated columns need to use HAVING not WHERE
• output type from a COUNT function is numeric, not character so quotes are not necessary around the 2

This will likely work:

having count(distinct id) = 2

---

@bhca60 wrote:

23         proc sql;
24         create table d
25         as select
26         name,
27         dob,
28         count (distinct id) as idct
29         from database
30         where calculated idct = "2"
31         group by name, dob;
ERROR: The following columns were not found as CALCULATED references in the immediate query: idct.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
32         quit;

I've tried with and without "calculated" and with and without quotes around the number; I'm just trying to get those with more than 1 as an idct so as to get those with duplicate data.

---

[mkeintz]

Since this is not a pass-thru application of PROC SQL, the responsibility for generating IDCT belongs to the sql procedure.  But WHERE filters are "outsourced" to the data retrieval engine, which is applied prior to the calculation of IDCT.  Hence, as @Reeza points out, the WHERE filter can not be applied to a calculated variable ... which means you will need to use her HAVING suggestion.