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...!!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: