SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

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

[ Edited ]

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?


Accepted Solutions
Solution
‎04-28-2017 05:58 AM
Respected Advisor
Posts: 4,173

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

Posted in reply to Ricardo_Neves

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

 

View solution in original post


All Replies
Super User
Posts: 5,432

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

Posted in reply to Ricardo_Neves

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

Data never sleeps
Occasional Contributor
Posts: 18

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

[ Edited ]

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?

Respected Advisor
Posts: 4,173

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

Posted in reply to Ricardo_Neves

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;

 

Occasional Contributor
Posts: 18

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

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;

 

Solution
‎04-28-2017 05:58 AM
Respected Advisor
Posts: 4,173

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

Posted in reply to Ricardo_Neves

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

 

Occasional Contributor
Posts: 18

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

[ Edited ]

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

Respected Advisor
Posts: 4,173

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

Posted in reply to Ricardo_Neves

@Ricardo_Neves

Good that it worked for you :-) 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 290 views
  • 1 like
  • 3 in conversation