Proc SQL (max) function

[SannaSanna]

Hello!

Would appreciate some help with my syntx.  I have two tables (A) and (B). I am trying to match all the records in table (A) to one of the many records in table (B)  Variable column  [B.IDCERTKEY] is a unique numerical primary key in table (B) with the most recent/newest record being the highest number.  There could be various [B.IDCERTKEY[ for one person identified as [(IDKEY)].  I want to tie the max(B.IDCERTKEY) to the matching person [IDKEY] to table A.  Can someone help me with the correct syntax so I can accomplish this?  My partial code is below.   Thank you!

PROC SQL;
 CREATE TABLE NEW AS   
SELECT A.*, B.IDKEY, B.IDCERTKEY, B.IDENCTYMSNGINDCTR, B.IDCATGCODE1KEY,
  B.IDCATGCODE2KEY, B.IDCATGCODE3KEY,B.IDCATGCODE4KEY,
  B.IDCATGCODE5KEY, 
FROM GROUPA AS A LEFT JOIN ID2B AS B
ON A.IDKEY=B.IDKEY AND (A.SAS_ID_START_DATE LE B.SAS_WITHDRLDATE_DATE LE A.SAS_ID_END_DATE) ;
QUIT;

[kannand]

Hello - Hope this what you are looking for ....

data GROUPA;
     input IDKEY:4. VAR:$1.;
     cards;
     2014 A
     2014 B
     2015 C
     2016 D
;
RUN;
data ID2B;
     input IDKEY:4. IDCERTKEY:2.;
     cards;
     2014 01 
     2014 02 
     2015 03 
     2016 04 
;
RUN;
PROC SQL;
 SELECT A.*,B.IDCERTKEY FROM GROUPA A,
 				 ID2B   B 			
  WHERE A.IDKEY = B.IDKEY
    AND B.IDCERTKEY = (SELECT MAX(DISTINCT IDCERTKEY) 
 			  			 FROM ID2B C
 			  			WHERE B.IDKEY = C.IDKEY) ;
QUIT;

Produces the following: (the right most column has the highest values that you are looking for

IDKEY	VAR	IDCERTKEY
2014	A	2
2014	B	2
2015	C	3
2016	D	4

Good Luck...!!!

[SannaSanna]

I did this:
PROC SQL;
SELECT A.*,B.IDCERTKEY FROM GROUPA A,
ID2B B
WHERE A.IDKEY = B.IDKEY
AND B.IDCERTKEY = (SELECT MAX(DISTINCT IDCERTKEY)
FROM ID2B C
WHERE B.IDKEY = C.IDKEY) and AND (p.SAS_STU_START_DATE LE e.SAS_WITHDRLDATE_DATE LE p.SAS_STU_END_DATE) ;
QUIT;

I added the date qualifier at the end of the code and Its taking quite awhile- did I put the date parameter in the correct context of the syntax?

[kannand]

You seem to have two ANDs. Please check the syntax.

[SannaSanna]

Ohh thank you thank you!!! It works!!! Is there a way I can see the 'join' statement in the syntax? I need a full left join so that all rows will return back for table (A) even if there is no match in table (B).

[kannand · Accepted solution]

Here is how it looks with the LEFT JOIN...

data GROUPA;
     input IDKEY:4. VAR:$1.;
     cards;
     2014 A
     2014 B
     2015 C
     2016 D
     2013 E
;
RUN;
data ID2B;
     input IDKEY:4. IDCERTKEY:2.;
     cards;
     2014 01 
     2014 02 
     2015 03 
     2016 04 
;
RUN;
PROC SQL;
 SELECT A.*,B.IDCERTKEY FROM GROUPA A left join 
 				 ID2B   B 			on 
  		A.IDKEY = B.IDKEY
    AND B.IDCERTKEY = (SELECT MAX(DISTINCT IDCERTKEY) 
 			  			 FROM ID2B C
 			  			WHERE B.IDKEY = C.IDKEY) ;
QUIT;

And the output shows:

IDKEY	VAR	IDCERTKEY
2013	E	.
2014	A	2
2014	B	2
2015	C	3
2016	D	4

Hope this helps...!!!

[SannaSanna]

ohh thank you so much! You are just awesome! I can now finally go home. Thank you again!

[LinusH]

You could try group by with having max (idcertkey).