BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ricardo_Neves
Obsidian | Level 7

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@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

7 REPLIES 7
LinusH
Tourmaline | Level 20

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
Ricardo_Neves
Obsidian | Level 7

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?

Patrick
Opal | Level 21

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;

 

Ricardo_Neves
Obsidian | Level 7

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;

 

Patrick
Opal | Level 21

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

 

Ricardo_Neves
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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