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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.