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-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!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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