BookmarkSubscribeRSS Feed
Crysis85
Obsidian | Level 7

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

 

 

2 REPLIES 2
PGStats
Opal | Level 21

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. 

 

PG
Crysis85
Obsidian | Level 7
Yes, well I don't doubt there are better way to do this than through VA (also, my mistake the date field is in table1 only), my question was more about what was the deal with VA generating SAS code that way.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 1061 views
  • 0 likes
  • 2 in conversation