GETTING ERROR

[PN00429118]

%MACRO testing;
proc sql;
select count (*)  into :A from test1.testing;

select count(*) into :B  from
(
select *  from test1.testing
UNION
select *  from test1.testing) T1;
quit;


%if &A=&B %then
%do;
%put testing is unique table;

%end;
%else
%put testing is non unique table;



sql;
select tablename
      into :inputtables separated by ',' from test1.tc_inputs2;
quit;

%put &inputtables;
%mend;

%testing;

[RW9]

So what is the question?  I do not see, test data, error log, required output, what the code is supposed to do etc.?  I can tell you that you are missing the word proc from the last sql step.  I can also say that I see no need for a macro here at all, please explain what you are doing and why.

For example Base SAS:

data _null_;
  merge sashelp.vtable (where=(libname="SASHELP" and memname="CLASS"))
        sashelp.vtable (where=(libname="SASHELP" and memname="CLASS") rename=(nobs=b_nobs));
  if nobs=b_nobs then put "Unique table";
  else put "Non unique table";
run;

[Kurt_Bremser]

It helps if you show the log.

My first idea is that you are missing a PROC before the second SQL.

What are you trying to accomplish, anyway?

[Astounding]

I could be wrong here, but ...

Even if you eliminate the error, willl the program ever work correctly?  My understanding of UNION is that it just appends records.  The result would always be that &A * 2 = &B.  You might want to describe what you are identifying as a "unique table" because you probably need to change the test that you are making.

[Kurt_Bremser]

---

@Astounding wrote:

I could be wrong here, but ...

Even if you eliminate the error, willl the program ever work correctly?  My understanding of UNION is that it just appends records.  The result would always be that &A * 2 = &B.  You might want to describe what you are identifying as a "unique table" because you probably need to change the test that you are making.

---

Per SQL norm, a UNION always implies DISTINCT, so the condition would always be true, making the whole test futile.

(Tested it with SASHELP.CLASS)

So we dearly need to know what the OP's intentions are.

[ballardw]

I think the OP is actually doing sort of Proc compare step.

Consider:

data testing;
   input x y z;
datalines;
1 2 3
4 5 6
4 5 6
;
run;

proc sql noprint;
   select count (*)  into :A 
   from testing;

   select count(*) into :B  from
      (
      select *  from testing
      UNION
      select *  from testing) 
   ;
quit;

%put A= &a   B=&b;

The counts do differ.

But you don't really know where the difference only that there is one or more duplicate records in the base set.

I'm not really sure if there is an advantage of the Union as opposed to Select Distinct though.

[RW9]

If that is indeed the case then why not simply (have just bashed this out quickly, not tested):

proc sql;
  create table MISS1 as
  select  *
  from    FIRST_TABLE
  except  (select * from SECOND_TABLE);
  create table MISS2 as
  select  *
  from    SECOND_TABLE
  except  (select * from FIRST_TABLE);
quit;

You get two datasets one for each missing from the other, easy to see what is missing then.

[Kurt_Bremser]

Yeah, that's it. Just a very complicated way of checking for duplicates.

proc sql noprint;
select count(*) into :A from testing;
select count (*) into :B from (select distinct * from testing);
quit;
data _null_;
if &A ne &B then put "not unique";
run;