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 !!
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.
Can you post som example data, please?
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 ?
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.