Help using Base SAS procedures

Proc SQL (max) function

Accepted Solution Solved
Reply
Contributor
Posts: 57
Accepted Solution

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;


Accepted Solutions
Solution
‎10-27-2015 09:43 PM
Regular Contributor
Posts: 161

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

View solution in original post


All Replies
Regular Contributor
Posts: 161

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
Contributor
Posts: 57

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?
Regular Contributor
Posts: 161

Re: Proc SQL (max) function

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

Kannan Deivasigamani
Contributor
Posts: 57

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).
Solution
‎10-27-2015 09:43 PM
Regular Contributor
Posts: 161

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
Contributor
Posts: 57

Re: Proc SQL (max) function

ohh thank you so much! You are just awesome! I can now finally go home. Thank you again!
Super User
Posts: 5,260

Re: Proc SQL (max) function

You could try group by with having max (idcertkey).
Data never sleeps
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 1241 views
  • 3 likes
  • 3 in conversation