topic Re: Proc SQL (max) function in SAS Procedures

Proc SQL (max) function

SannaSanna — Tue, 27 Oct 2015 21:33:43 GMT

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;

Re: Proc SQL (max) function

kannand — Tue, 27 Oct 2015 22:18:23 GMT

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

Re: Proc SQL (max) function

LinusH — Tue, 27 Oct 2015 22:19:09 GMT

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

Re: Proc SQL (max) function

SannaSanna — Tue, 27 Oct 2015 22:42:23 GMT

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?

Re: Proc SQL (max) function

kannand — Tue, 27 Oct 2015 22:48:15 GMT

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

Re: Proc SQL (max) function

SannaSanna — Wed, 28 Oct 2015 01:07:34 GMT

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

Re: Proc SQL (max) function

kannand — Wed, 28 Oct 2015 01:31:10 GMT

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

Re: Proc SQL (max) function

SannaSanna — Wed, 28 Oct 2015 01:44:32 GMT

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