topic Re: Abort job if source and compare tables are different - SAS DI Studio 4.9 in SAS Data Management

Abort job if source and compare tables are different - SAS DI Studio 4.9

Ricardo_Neves — Tue, 11 Apr 2017 14:30:24 GMT

Hi,

I'm trying to compare the result of two queries that should return the same results.

I'm using the compare table transformation to compare the resulting tables and I want the Job to abort in case the tables are different, i.e. if any of the tables besides the 'unchanged records' table has any records.

Any idea of how to do this?

Re: Abort job if source and compare tables are different - SAS DI Studio 4.9

LinusH — Tue, 11 Apr 2017 10:51:47 GMT

I haven't used that particular transformation, neither have access to DIS at the moment, so this will be a "paper" reply.

Depending on how the "other tables" are created, you would migh be able to use Status Handling (data modified...?).

Another option is to have a post process/user written transformation that checks for no of records in &SYSLAST (or whatever works in the current situation).

Re: Abort job if source and compare tables are different - SAS DI Studio 4.9

Ricardo_Neves — Tue, 11 Apr 2017 11:04:20 GMT

This transformation connects 2 tables (one as source and another as a compare table) through a key column (in this case i'm using a date) and compares the columns I designate. Then it populates 4 tables: New records, Changed records, Unchanged records, Missing records.

There is no Status Handling Option in this transformation and usually the status handling only has options to abort if the table is empty, not if it is not empty...

I'm sorry but I'm fairly new to SAS, how would I verify if the tables are empty and abort if not in a user written code?

Re: Abort job if source and compare tables are different - SAS DI Studio 4.9

Patrick — Tue, 11 Apr 2017 11:56:35 GMT

I haven't used this transformation either so just answering your question how to retrieve the number of rows from a SAS table.

You could always do a SQL count(*) but with SAS tables the number of rows in a table is also a table attribute which you can query without the need to pass through the actual data.

/* option 1 */
data _null_;
  call symputx('NobsInTable1',NumObsInTable);
  stop;
  set sashelp.class nobs=NumObsInTable;
run;
%put &=NobsInTable1;

/* option 2 */
proc sql noprint;
  select nobs into :NobsInTable2
  from dictionary.tables
  where libname='SASHELP' and memname='CLASS'
  ;
quit;
%put &=NobsInTable2;

/* Extended option 2: rowcount sum for multiple tables */
proc sql noprint;
  select sum(nobs) into :NobsInTableN
  from dictionary.tables
  where libname='SASHELP' and memname in ('CLASS','COMPANY')
  ;
quit;
%put &=NobsInTableN;

Re: Abort job if source and compare tables are different - SAS DI Studio 4.9

Ricardo_Neves — Tue, 11 Apr 2017 14:41:44 GMT

I think I solved it but I had to use a User Written transformation.

Here is the code I'm using, in case someone comes across a similar problem.

data _null_;
  if eof then
    do;
     
     put 'NOTE: EOF - DATASET vazio';
    end;
	else do;
		call symput('dsempty',1);
	end;
  set &_input1 end=eof;
run;

data _null_;
  if eof then
    do;
     
     put 'NOTE: EOF - DATASET vazio';
    end;
	else do;
		call symput('dsempty',1);
	end;
  set &_input2 end=eof;
run;

data _null_;
  if eof then
    do;
     
     put 'NOTE: EOF - DATASET vazio';
    end;
	else do;
		call symput('dsempty',1);
	end;
  set &_input3 end=eof;
run;

%macro Compare;
 %if &dsempty = 1 %then %do;
 %abort;
 %end;
%mend;


%Compare;

Re: Abort job if source and compare tables are different - SAS DI Studio 4.9

Patrick — Tue, 11 Apr 2017 23:03:37 GMT

@Ricardo_Neves

You're still having a full pass through the data which is not necessary. Here an alternative option which doesn't need to process the data.

data test1 test2 test3;
/*  stop;*/
  set sashelp.class;
run;
%let _input1=test1;
%let _input2=test2;
%let _input3=test3;

/* Extended option 2: rowcount sum for multiple tables */
%let NobsInTables=0;
proc sql noprint feedback;
  select sum(nobs) into :NobsInTables
  from dictionary.tables
  where 
    libname="%scan(WORK.%upcase(&_input1),-2,.)"
    and memname in 
                  (
                    "%scan(%upcase(&_input1),-1,.)",
                    "%scan(%upcase(&_input2),-1,.)",
                    "%scan(%upcase(&_input3),-1,.)"
                    )
  ;
quit;

%put &=NobsInTables;

data _null_;
  if &NobsInTables < 1 then
    do;
      put 'Tables not identical';
      put 'Aborting job...., Aborting job....';
      abort return 10;    
    end;
  stop;
run;

Re: Abort job if source and compare tables are different - SAS DI Studio 4.9

Ricardo_Neves — Thu, 27 Apr 2017 10:50:32 GMT

@Patrick the first time I tryed your solution it didin't work and I didn't really understand why, but now I understood your code better and realised it has to be

data _null_;
  if &NobsInTables > 0 then
    do;
      put 'Tables not identical';
      put 'Aborting job...., Aborting job....';
      abort return 10;    
    end;
  stop;
run;

because I want to abort if there are any observations in the table.

This really is a better option. Thanks for your help.

Re: Abort job if source and compare tables are different - SAS DI Studio 4.9

Patrick — Fri, 28 Apr 2017 08:47:57 GMT

@Ricardo_Neves

Good that it worked for you 🙂