DATA Step, Macro, Functions and more

GETTING ERROR

Reply
Occasional Contributor
Posts: 5

GETTING ERROR

%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;

Super User
Super User
Posts: 7,988

Re: GETTING ERROR

[ Edited ]
Posted in reply to PN00429118

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;
Super User
Posts: 7,854

Re: GETTING ERROR

Posted in reply to PN00429118

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?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,516

Re: GETTING ERROR

Posted in reply to PN00429118

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.

Super User
Posts: 7,854

Re: GETTING ERROR

Posted in reply to Astounding

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 11,343

Re: GETTING ERROR

[ Edited ]
Posted in reply to Astounding

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.

Super User
Super User
Posts: 7,988

Re: GETTING ERROR

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.

Super User
Posts: 7,854

Re: GETTING ERROR

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 7 replies
  • 277 views
  • 0 likes
  • 5 in conversation