Complex Join in PROC SQL

[AJRowe]

I'm trying to convert a join that works in SQL to an equivalent in PROC SQL.

One table has a listing of test results.  A separate table has a new test result that is less timely than the first table.  I want the less timely result from the 2nd table to be repeated in the 1st table until a new 2nd table result comes along.

PROC SQL;
   CREATE TABLE WORK.QUERY_FOR_PROCESS_LAB_CORRELATED AS 
   SELECT t1.PLANT_NUM, 
          t1.COMMODITY_COD, 
          t1.COMMODITY_NAM, 
          t1.LAB_BOARD_DAT_COD, 
          t1.PLANT_NAM, 
          t2.Purity_Avg_pct
      FROM WORK.PURITY t2
    left outer join
    MFGNPRO.PROCESS_LAB_CORRELATED_TAB t1
     ON  (t1.PLANT_NUM = t2.PLANT_NUM) AND
     t2.DateTime = ( SELECT MAX(WORK.PURITY.DateTime) FROM WORK.PURITY 
	WHERE WORK.PURITY.DateTime <= t1.LAB_BOARD_DAT_COD);
QUIT;

The error I get seems to be around MAX(WORK.PURITY.DateTime) : ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, ), *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.  

I'm using Enterprise Guide versoin 7.13 HF5 64-bit

Thanks

[Reeza]

Can you post your actual log please. 

[Yegen · Accepted solution]

It would be better if you post a sample and what the output should look like.

Maybe this example will help out. Here are two samples I had quick access to:

data work.sample1;
infile cards expandtabs truncover;
input mgrno	announcement : mmddyy10.;
format announcement mmddyy10.;
cards;
9385	9/16/09
62890	9/14/08
;
run;


data work.sample2;
 infile cards expandtabs truncover;
input mgrno	fdate  : mmddyy10.	cusip;
format fdate mmddyy10.;
cards;
9385	12/31/07	12345
9385	12/31/06	12345
9385	12/31/08	12345
9385	12/31/09	12345
9385	12/31/10	12345
9385	12/31/11	12345
9385	12/31/07	23456
9385	12/31/08	23456
9385	12/31/07	34567
9385	12/31/07	78911
9385	12/31/08	78912
62890	9/13/08	34567
62890	9/13/07	12345
62890	9/13/06	12345
62890	9/13/11	22345
;
run;

As far as I can understand want you want to do, the code will look like the following:

PROC SQL;
   CREATE TABLE work.want AS 
   SELECT a.mgrno, a.announcement, b.fdate
   FROM work.sample1 as a 
   LEFT JOIN work.sample2 as b 
   ON  a.mgrno = b.mgrno 
   WHERE b.fdate <= a.announcement
   GROUP BY b.mgrno HAVING b.fdate=max(b.fdate);
QUIT; 

[AJRowe]

That query structure worked for me.  Thanks!

[AJRowe]

Will do, next time.  Thanks

[LinusH]

Of you just want to fix your existing SQL: SAS SQL doesn't allow three level naming. Since your sub query is with a WORK table you'll be fine just to remove that part since WORK is implied when the linked is omitted. If you are in a similar situation with a table from a permanent library you need to use an alias on the from clause.

[AJRowe]

Thanks for the suggestions.