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;
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...!!!
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...!!!
You seem to have two ANDs. Please check the syntax.
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...!!!
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!
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.
Ready to level-up your skills? Choose your own adventure.