topic Re: delete records from the table , even if in of the table the field value is "N" in SAS Programming

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

librasonali — Tue, 08 Feb 2022 07:36:31 GMT

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 !!

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

PeterClemmensen — Tue, 08 Feb 2022 07:26:51 GMT

Can you post som example data, please?

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

Kurt_Bremser — Tue, 08 Feb 2022 07:31:42 GMT

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.

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

librasonali — Tue, 08 Feb 2022 07:34:13 GMT

UPDATED my question

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

librasonali — Tue, 08 Feb 2022 07:34:41 GMT

hey thanks !!

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

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

Kurt_Bremser — Tue, 08 Feb 2022 07:36:45 GMT

DATA step with DATALINES.

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

PeterClemmensen — Tue, 08 Feb 2022 07:40:49 GMT

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 ?

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

librasonali — Tue, 08 Feb 2022 08:02:38 GMT

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.

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

librasonali — Tue, 08 Feb 2022 08:28:04 GMT

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