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. 🙂
... View more