Hi,
I have a table A, and table B.
I want to write a macro function to clean up table A if any of User ID, Customer ID, or Email_Add that exist in table A and B, then those rows would be deleted for A. (table B remains unchanged)
I continue from the previous post https://communities.sas.com/t5/SAS-Programming/Delete-an-entry-if-one-of-the-column-values-appeared-in-other/m-p/838321#M331450
and start to write a macro function
%macro CLEANED_TB(User_ID, Customer_ID, Email_add, Table_Required);
the whole script like below,
data A;
input User_ID $ Customer_ID $ Email_add :$20. Invoice_no $ Invoice_account;
datalines;
u1 c1 example1@gmail.com v1 100
u2 c2 example2@gmail.com v2 120
u2 c2 example2b@gmail.com v3 130
u3 c3 example3@gmail.com v4 150
u4 c4 example4@gmail.com v5 165
u5 c5 example5@gmail.com v6 180
;
data B;
input User_ID $ Customer_ID $ Email_add :$20.;
infile datalines missover dlm = '|';
datalines;
| c1 |example1@gmail.com
| |example2@gmail.com
u3 | |
;
%macro CLEANED_TB(User_ID, Customer_ID, Email_add, Table_Required);
data work.B;
input B;
run;
create table &Table_Required as
select * from &Table_Required
where &User_ID not in (select User_ID from b)
and &Customer_ID not in (select Customer_ID from b)
and &Email_add not in (select Email_add from b)
quit;
%mend;
%CLEANED_TB(User_ID=&User_ID, Customer_ID=&Customer_ID, Email_add=&Email_add, Table_Required=&A);
It run with error.
Please help.
Thanks
... View more
