topic Re: GETTING ERROR in SAS Programming

GETTING ERROR

PN00429118 — Wed, 23 Mar 2016 14:16:32 GMT

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

Re: GETTING ERROR

RW9 — Wed, 23 Mar 2016 14:28:11 GMT

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;

Re: GETTING ERROR

Kurt_Bremser — Wed, 23 Mar 2016 14:25:20 GMT

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?

Re: GETTING ERROR

Astounding — Wed, 23 Mar 2016 14:36:28 GMT

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.

Re: GETTING ERROR

Kurt_Bremser — Wed, 23 Mar 2016 15:05:07 GMT

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

Re: GETTING ERROR

ballardw — Wed, 23 Mar 2016 15:27:03 GMT

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.

Re: GETTING ERROR

RW9 — Wed, 23 Mar 2016 15:21:08 GMT

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.

Re: GETTING ERROR

Kurt_Bremser — Wed, 23 Mar 2016 15:29:25 GMT

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;