topic Re: Where is not working in SAS Programming

Where is not working

bhca60 — Wed, 01 Jun 2022 20:50:55 GMT


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.

Re: Where is not working

Reeza — Wed, 01 Jun 2022 20:55:23 GMT

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.

Re: Where is not working

mkeintz — Wed, 01 Jun 2022 21:49:42 GMT

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.