Filter a locate a duplicate ID by additional infromation

[sarahzhou]

Hi Instructurs,

I have a table work.RL_INFO

INDV_KEY	PROF_REF_NO	MEMBER_ID	USER_ID	OTH_ID	NM_TX	BRTH_DT	SUBJ_INDV_KEY	TO_INDV_KEY	RLTN_TX	RLTN_STRT_DT	RLTN_END_DT
651475EF7	651475EF7F8B			S93	ABB	7-Jul-93	2742A6C99A4296324EFF2017	651475EF7F8BC23D80063C	Member	7-Jul-93	.
651475EF8	651475EF7F8B			S93	ACC	10-Jul-93	2742A6C99A4296324EFF2018	651475EF7F8BC23D80064C

I wrote a query,

proc sql; 
	create table RL as 
	select distinct
        RLTN_TX as Rel 
		,MEMBER_ID
		,USER_ID
        ,BRTH_DT

	from WORK.RL_INFO 
    WHERE MEMBER_ID=&INPUTID. or USER_ID=&INPUTID. or OTH_ID =&INPUTID. 
    group by Rel
	HAVING RelEndDT = MAX(RelEndDT)
	;quit;

It was designed where eithter MEMBER_ID , USER_ID, OTH_ID can be treated as input variable &INPUTID. for query the Rel information. However, when If encounger a duplicated OTH_ID, then I need a additional information BRTH_DT as a mandatory input &INPUTDOB. to locate that subject.

I want the first row in my table work.RL_INFO be the out put for sql table RL.

I've tried to change where clause to,

WHERE MEMBER_ID=&INPUTID. or USER_ID=&INPUTID. or OTH_ID =&INPUTID. or BRTH_DT=&INPUTDOB.

It didn't work.

How do I do that?

Thanks

[andreas_lds · Accepted solution]

Changing the last OR to AND should be closer to what you expect.

[sarahzhou]

yes, it is. Thanks