delete records from the table , even if in of the table the field value is "N"

[librasonali]

Hi,

I have multiple tables coming from teradata. Example Table A , Table B , Table C 
using these 3 tables ( joining -- left join using proc sql) I am creating one table suppose Table Final .

Now in my Table A , Table B and Table C I have one field as FLAG. So, Now i want to delete the records even if in any of the table A , B or C the field FLAG="N" .

that is in my Table Final i don't want records with FLAG="N". 

the problem is that even if in any of the table it's N the record should be deleted. 

SAMPLE DATA :

TABLE A
PHRM_SRVC_PROV_KEY	PHRM_CLAIM_KEY	CLM_ORG_FLAG_IND	SRVC_DT	PROD_SRVC_ID	FLAG
125	1853	B	1-Jan-20	23155050110	Y
79	20022	B	5-Jul-20	11917004813	Y
138	10703	B	2-Mar-20	13107015790	N
153	2669	B	2-Apr-20	49884002101	N
90	6339	B	1-Feb-20	31722072250	Y

TABLE B
PHRM_SRVC_PROV_KEY	CST_TYP_CD	DRG_UNIT_CST	PAY_TYP_IND	INGR_BIL_CST	INGR_SBMT_CST	FLAG
125	MACHU	0.6478	P	0.05	25	Y
79	AWP	0.1999	P	4.9	25	Y
138	MACHU	2.973	P	6.95	25	N
153	MACHU	1.0836	P	5.86	50	N
90	MACHU	19.2878	P	8.01	10	Y

TABLE C
PHRM_SRVC_PROV_KEY	INGR_SBMT_CST	INGR_CALC_CST	BIL_DSPNS_FEE	FLAG
125	25	0.05	0.38	Y
79	25	4.9	0.38	Y
138	25	6.95	0.38	N
153	50	5.86	0.38	N
90	10	8.01	0.38	Y

TABLE FINAL
PHRM_SRVC_PROV_KEY	FLAG
125	Y
79	Y
90	Y

Hope any body helps me , thanks !!

[PeterClemmensen]

Can you post som example data, please?

[librasonali]

UPDATED my question

[PeterClemmensen]

What if FLAG = Y for PHRM_SRVC_PROV_KEY = 138 in Table A and FLAG = N for PHRM_SRVC_PROV_KEY = 138 in Table B ?

[librasonali]

ideally it should not happen ... , but there fore for that situation i want that even if the flag="N" in any of the 3 table it shd be excluded.

[Kurt_Bremser · Accepted solution]

Create a new variable, and subset on that with a having clause:

proc sql;
create table want as
  select
    a.something,
    b.something,
    c.something,
    case
      when a.flag = 'N' or b.flag = 'N' or c.flag = 'N'
      then 'N'
      else 'Y'
    end as new_flag
  from a left join b
  on <condition>
  left join c
  on <condition>
  having new_flag = 'Y'
;
quit;

Obviously untested; for tested code, supply example data in data steps with datalines, and what you expect to get out of this example data.

[librasonali]

hey thanks !!

i want my records to be deleted , i have updated my questions with some sample data

[Kurt_Bremser]

DATA step with DATALINES.

[librasonali]

i can't use having because i am already using where condition , so i used where
new_flag = 'Y'