Delete a row in a table if the columns value exist in another table

[sarahzhou]

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

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

[PaigeMiller]

When you have macros that run with error, show us the ENTIRE log for this macro.

First, turn on macro debugging options:

options mprint symbolgen;

Then run the code again and show us the ENTIRE log for this macro by copying it as text and pasting it into the window that appears when you click on the </> icon.

[sarahzhou]

this is the entire log

 
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 68         
 69         options mprint symbolgen;
 70         data A;
 71         input User_ID $ Customer_ID $ Email_add :$20. Invoice_no $ Invoice_account;
 72         datalines;
 
 NOTE: The data set WORK.A has 6 observations and 5 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              670.34k
       OS Memory           23972.00k
       Timestamp           24/10/2022 02:57:56 AM
       Step Count                        24  Switch Count  2
       Page Faults                       0
       Page Reclaims                     168
       Page Swaps                        0
       Voluntary Context Switches        9
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           264
       
 
 79         ;
 80         
 81         
 82         data B;
 83         input User_ID $ Customer_ID $ Email_add :$20.;
 84         infile datalines missover dlm = '|';
 85         datalines;
 
 NOTE: The data set WORK.B has 3 observations and 3 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              666.65k
       OS Memory           23972.00k
       Timestamp           24/10/2022 02:57:56 AM
       Step Count                        25  Switch Count  2
       Page Faults                       0
       Page Reclaims                     100
       Page Swaps                        0
       Voluntary Context Switches        12
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           264
       
 
 89         ;
 90         
 91         
 92         %macro CLEANED_TB(User_ID, Customer_ID, Email_add, Table_Required);
 93         data work.B;
 94         set B;
 95         run;
 96         
 97         create table &Table_Required as
 98            select * from &Table_Required
 99            where &User_ID     not in (select User_ID     from b)
 100             and &Customer_ID not in (select Customer_ID from b)
 101             and &Email_add   not in (select Email_add   from b)
 102        quit;
 103        %mend;
 104        
 105        %CLEANED_TB(User_ID=&User_ID, Customer_ID=&Customer_ID, Email_add=&Email_add, Table_Required=&A);
 WARNING: Apparent symbolic reference USER_ID not resolved.
 WARNING: Apparent symbolic reference CUSTOMER_ID not resolved.
 WARNING: Apparent symbolic reference EMAIL_ADD not resolved.
 WARNING: Apparent symbolic reference A not resolved.
 ERROR: The text expression &USER_ID contains a recursive reference to the macro variable USER_ID.  The macro variable will be 
        assigned the null value.
 ERROR: The text expression &CUSTOMER_ID contains a recursive reference to the macro variable CUSTOMER_ID.  The macro variable will 
        be assigned the null value.
 ERROR: The text expression &EMAIL_ADD contains a recursive reference to the macro variable EMAIL_ADD.  The macro variable will be 
        assigned the null value.
 MPRINT(CLEANED_TB):   data work.B;
 MPRINT(CLEANED_TB):   set B;
 MPRINT(CLEANED_TB):   run;
 
 NOTE: There were 3 observations read from the data set WORK.B.
 NOTE: The data set WORK.B has 3 observations and 3 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       user cpu time       0.01 seconds
       system cpu time     0.00 seconds
       memory              939.21k
       OS Memory           24232.00k
       Timestamp           24/10/2022 02:57:56 AM
       Step Count                        26  Switch Count  2
       Page Faults                       0
       Page Reclaims                     144
       Page Swaps                        0
       Voluntary Context Switches        12
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           264
       
 
 NOTE: Line generated by the invoked macro "CLEANED_TB".
 105                                    create table &Table_Required as    select * from &Table_Required    where &User_ID     not in
                                        ______
                                        180
 105      !  (select User_ID     from b)      and &Customer_ID not in (select Customer_ID from b)      and &Email_add   not in
 105      ! (select Email_add
 SYMBOLGEN:  Macro variable TABLE_REQUIRED resolves to &A
 WARNING: Apparent symbolic reference A not resolved.
 SYMBOLGEN:  Macro variable TABLE_REQUIRED resolves to &A
 WARNING: Apparent symbolic reference A not resolved.
 SYMBOLGEN:  Macro variable USER_ID resolves to 
 SYMBOLGEN:  Macro variable CUSTOMER_ID resolves to 
 SYMBOLGEN:  Macro variable EMAIL_ADD resolves to 
 MPRINT(CLEANED_TB):   create table &A as select * from &A where not in (select User_ID from b) and not in (select Customer_ID from 
 b) and not in (select Email_add from b) quit;
 
 ERROR 180-322: Statement is not valid or it is used out of proper order.
 
 106        
 107        OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 SYMBOLGEN:  Macro variable GRAPHTERM resolves to GOPTIONS NOACCESSIBLE;
 117        

the Marco variable is not resolved.

How can I fix this, thanks!

[sarahzhou · Accepted solution]

I think I fixed this...

options mprint symbolgen;
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;
set B;
run;

proc sql;
create table &Table_Required as
   select * from &Table_Required
   where &Table_Required..User_ID     not in (select User_ID     from b)
     and &Table_Required..Customer_ID not in (select Customer_ID from b)
     and &Table_Required..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);

proc print data=A;
run;

 
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 SYMBOLGEN:  Macro variable _SASWSTEMP_ resolves to /home/u60008750/.sasstudio/.images/6722e3bc-3fd1-4c45-bff5-f38bd5a5e598
 SYMBOLGEN:  Some characters in the above value which were subject to macro quoting have been unquoted for printing.
 SYMBOLGEN:  Macro variable GRAPHINIT resolves to GOPTIONS RESET=ALL GSFNAME=_GSFNAME;
 68         
 69         options mprint symbolgen;
 70         data A;
 71         input User_ID $ Customer_ID $ Email_add :$20. Invoice_no $ Invoice_account;
 72         datalines;
 
 NOTE: The data set WORK.A has 6 observations and 5 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       user cpu time       0.01 seconds
       system cpu time     0.00 seconds
       memory              671.40k
       OS Memory           25508.00k
       Timestamp           24/10/2022 03:18:12 AM
       Step Count                        124  Switch Count  2
       Page Faults                       0
       Page Reclaims                     113
       Page Swaps                        0
       Voluntary Context Switches        11
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           272
       
 
 79         ;
 80         
 81         
 82         data B;
 83         input User_ID $ Customer_ID $ Email_add :$20.;
 84         infile datalines missover dlm = '|';
 85         datalines;
 
 NOTE: The data set WORK.B has 3 observations and 3 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              674.84k
       OS Memory           25508.00k
       Timestamp           24/10/2022 03:18:12 AM
       Step Count                        125  Switch Count  2
       Page Faults                       0
       Page Reclaims                     95
       Page Swaps                        0
       Voluntary Context Switches        14
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           264
       
 
 89         ;
 90         
 91         
 92         %macro CLEANED_TB(User_ID, Customer_ID, Email_add, Table_Required);
 93         data work.B;
 94         set B;
 95         run;
 96         
 97         proc sql;
 98         create table &Table_Required as
 99            select * from &Table_Required
 100           where &Table_Required..User_ID     not in (select User_ID     from b)
 101             and &Table_Required..Customer_ID not in (select Customer_ID from b)
 102             and &Table_Required..Email_add   not in (select Email_add   from b);
 103        quit;
 104        %mend;
 105        
 106        %CLEANED_TB(User_ID=User_ID, Customer_ID=Customer_ID, Email_add=Email_add, Table_Required=A);
 MPRINT(CLEANED_TB):   data work.B;
 MPRINT(CLEANED_TB):   set B;
 MPRINT(CLEANED_TB):   run;
 
 NOTE: There were 3 observations read from the data set WORK.B.
 NOTE: The data set WORK.B has 3 observations and 3 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       user cpu time       0.01 seconds
       system cpu time     0.00 seconds
       memory              939.28k
       OS Memory           25768.00k
       Timestamp           24/10/2022 03:18:12 AM
       Step Count                        126  Switch Count  2
       Page Faults                       0
       Page Reclaims                     127
       Page Swaps                        0
       Voluntary Context Switches        11
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           264
       
 
 MPRINT(CLEANED_TB):   proc sql;
 SYMBOLGEN:  Macro variable TABLE_REQUIRED resolves to A
 SYMBOLGEN:  Macro variable TABLE_REQUIRED resolves to A
 SYMBOLGEN:  Macro variable TABLE_REQUIRED resolves to A
 SYMBOLGEN:  Macro variable TABLE_REQUIRED resolves to A
 SYMBOLGEN:  Macro variable TABLE_REQUIRED resolves to A
 MPRINT(CLEANED_TB):   create table A as select * from A where A.User_ID not in (select User_ID from b) and A.Customer_ID not in 
 (select Customer_ID from b) and A.Email_add not in (select Email_add from b);
 WARNING: This CREATE TABLE statement recursively references the target table. A consequence of this is a possible data integrity 
          problem.
 NOTE: Table WORK.A created, with 3 rows and 5 columns.
 
 MPRINT(CLEANED_TB):   quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              5910.56k
       OS Memory           30892.00k
       Timestamp           24/10/2022 03:18:12 AM
       Step Count                        127  Switch Count  2
       Page Faults                       0
       Page Reclaims                     199
       Page Swaps                        0
       Voluntary Context Switches        12
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           296
       
 
 107        
 108        proc print data=A;
 109        run;
 
 NOTE: There were 3 observations read from the data set WORK.A.
 NOTE: PROCEDURE PRINT used (Total process time):
       real time           0.01 seconds
       user cpu time       0.01 seconds
       system cpu time     0.00 seconds
       memory              1230.81k
       OS Memory           25764.00k
       Timestamp           24/10/2022 03:18:12 AM
       Step Count                        128  Switch Count  0
       Page Faults                       0
       Page Reclaims                     88
       Page Swaps                        0
       Voluntary Context Switches        0
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           8
       
 
 110        
 111        OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 SYMBOLGEN:  Macro variable GRAPHTERM resolves to GOPTIONS NOACCESSIBLE;
 121        

Thank you for the hints. 🙂

[Kurt_Bremser]

data work.B;
input B;
run;

You have no  INFILE or DATALINES statement here, so the INPUT has nothing to read from.

And your CREATE TABLE is not part of a PROC SQL, so it is syntactically invalid.

Rule #1 of macro development: start with working non-macro code before trying to make it dynamic.

[PGStats]

I added two features to the data to make this example more general

1) an empty condition in dataset B (which should be ignored)

2) I made Customer_Id numeric in both datasets

data A;
input User_ID $ Customer_ID Email_add :$20. Invoice_no $ Invoice_account;
datalines;
u1 1 example1@gmail.com  v1 100
u2 2 example2@gmail.com  v2 120
u2 2 example2b@gmail.com v3 130
u3 3 example3@gmail.com  v4 150
u4 4 example4@gmail.com  v5 165
u5 5 example5@gmail.com  v6 180
;

 
data B;
input User_ID $ Customer_ID Email_add :$20.;
infile datalines missover dlm = '|';
datalines;
   |  1 |example1@gmail.com
   |    |example2@gmail.com
u3 |    |                   
   |    |                   
;   


proc sql;
select * from A;
select * from B;
delete from A 
where exists (
    select * from B 
    where 
        cmiss(User_Id, Customer_Id, Email_Add) < 3 and
        ((User_Id=A.User_Id) + (Customer_Id=A.Customer_Id) + (Email_Add=A.Email_Add)) = 
        (3 - cmiss(User_Id, Customer_Id, Email_Add))   );
select * from A;
quit;