BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SannaSanna
Quartz | Level 8

 

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

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

7 REPLIES 7
kannand
Lapis Lazuli | Level 10

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
SannaSanna
Quartz | Level 8
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
Lapis Lazuli | Level 10

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

Kannan Deivasigamani
SannaSanna
Quartz | Level 8
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
Lapis Lazuli | Level 10

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
SannaSanna
Quartz | Level 8
ohh thank you so much! You are just awesome! I can now finally go home. Thank you again!
LinusH
Tourmaline | Level 20
You could try group by with having max (idcertkey).
Data never sleeps

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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