Dear community, I have 2 sample datasets, one is accounts table and other is table with related accounts. I need to join these tables and delete the accounts that are related. I need to have non related account only. data ACCTS;
infile cards dlm=',' MISSOVER dsd;
input ACT_NBR $ FNAME $ LNAME $ RELATION $ CNAME1 $ CNAME2 $ ;
If CNAME1 NE '' AND CNAME2 NE '' then do;
FULLNAME=STRIP(FNAME)||' '||STRIP(LNAME);
END;
ELSE DO;
FULLNAME=STRIP(CNAME1)||''||STRIP(CNAME2);
END;
CARDS;
1234,MIKE,MEYERS,JOINT,,
1234,MARY,MEYERS,JOINT,,
1234,JAY,MEYERS,JOINT-SO,,
2345,,,,MEYERS,TRUST
;
RUN;
data ACCTS_RELATION;
infile cards dlm=',' MISSOVER dsd;
input ACT_NBR $ FNAME $ LNAME $ RELATION $ CNAME1 $ CNAME2 $ ;
If CNAME1 NE '' AND CNAME2 NE '' then do;
FULLNAME=STRIP(FNAME)||' '||STRIP(LNAME);
END;
ELSE DO;
FULLNAME=STRIP(CNAME1)||''||STRIP(CNAME2);
END;
CARDS;
1234,MIKE,MEYERS,JOINT,,
1234,MARY,MEYERS,JOINT,,
1234,JAY,MEYERS,JOINT,,
1234,SARAH,COOKS,NOREL,,
1234,SHELLY,ROSS,JOINT-DA,,
3456,MIKE,MEYERS,JOINT,,
3456,MARY,MEYERS,JOINT,,
3456,JAY,MEYERS,JOINT-SO,,
5678,MIKE,MEYERS,JOINT,,
2345,,,,MEYERS,TRUST
7891,,,,MEYERS,TRUST
;
RUN; I need to have the output that is no relation to the accounts table (eg:1234,SARAH,COOKS,NOREL,,) only one record. I tried to make a full name and join both tables, but not able to get the output. The real data is huge, but I just wanted to get the concept here so that I can implement in bigger tables. Thanks in advance.
... View more
