Quartz | Level 8

## Proc SQL (max) function

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Lapis Lazuli | Level 10

## Re: Proc SQL (max) function

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

Kannan Deivasigamani
7 REPLIES 7
Lapis Lazuli | Level 10

## Re: Proc SQL (max) function

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

Kannan Deivasigamani
Quartz | Level 8

## Re: Proc SQL (max) function

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?
Lapis Lazuli | Level 10

## Re: Proc SQL (max) function

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

Kannan Deivasigamani
Quartz | Level 8

## Re: Proc SQL (max) function

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).
Lapis Lazuli | Level 10

## Re: Proc SQL (max) function

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

Kannan Deivasigamani
Quartz | Level 8

## Re: Proc SQL (max) function

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

## Re: Proc SQL (max) function

You could try group by with having max (idcertkey).
Data never sleeps
Discussion stats
• 7 replies
• 10782 views
• 3 likes
• 3 in conversation