BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AJRowe
Calcite | Level 5

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Yegen
Pyrite | Level 9

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; 

 

View solution in original post

6 REPLIES 6
Reeza
Super User

Can you post your actual log please. 

Yegen
Pyrite | Level 9

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
Calcite | Level 5

That query structure worked for me.  Thanks!

AJRowe
Calcite | Level 5

Will do, next time.  Thanks

LinusH
Tourmaline | Level 20
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.
Data never sleeps
AJRowe
Calcite | Level 5

Thanks for the suggestions.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 2240 views
  • 0 likes
  • 4 in conversation