07-19-2016 10:39 AM - edited 07-19-2016 10:44 AM
Hello, Working on Visual Analytics 7.1 I have to join two tables table1 and table2 selecting only the observation with the MAX date_field in table2
So I open the data builder, create a new query, set up the join, select all the colums from both table that interest me and in the WHERE tab I write
table1.date_field = ( SELECT MAX(table1.date_field) FROM library.table1 );
I save the query and try to validate it but an error appear. Checking the log I see that the SAS code generated is
FROM 31 library.table1 table1 32 INNER JOIN 33 library.table2 table2 34 ON table1.id = table2.id 35 WHERE 36 table1.date_field= ( 37 SELECT 38 table1.MAX (table1.date_field) _ 22 76 ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=. ERROR 76-322: Syntax error, statement will be ignored.
which is quite different from what I wrote. Maybe I'm missing something that I'm not seeing right now.
Thanks in advance.
PS: modifying by hand the generated sas code so that the subquery is written correctly (basically removing the "table1." in front of MAX) the query works and the resulting table is generated without issue. Yet it isn't an optimal soluction
07-19-2016 01:52 PM
I don't use VA, but SQL being SQL, it looks like you don't need a join at all
Proc sql; create table table3 as select * from table1 where date_field = (select max(date_field) from table2 where table1.id=table2.id); quit;
should be enough.
07-19-2016 02:00 PM