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-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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