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.
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.
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.
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.