CODEWITHSUNDEEP
sqlsas
Lovnlust
Lovnlust

Reputation: 1537

select maximum value of common column for multiple data set

I have a daily schedule process flow which refreshes a bunch of tables within the same library. At the end of the process flow, all tables should have the same up to date records. And I want to double check this via checking the maximum value of date.

But problem is, how can I quickly extract all max value of date among these tables and then compare them?

proc sql;
select max(date) from lib.table1;
select max(date) from lib.table2;
select max(date) from lib.table3;
...
quit;

Upvotes: 1

Views: 256

Answers (3)

Reeza
Reeza

Reputation: 21294

Create a view that appends all the tables with the date variable and select the max date from the variable. If your tables don't have the same structure you can modify the set statement to keep only the date variable. You may want to anyways to speed up the process.

data max_date/view=max_date;
   set table: indsname=source;
   dset=source;
   keep date dset;
run;

proc sql;
  create table maximum_date as
  select max(date) as Latest_Date 
  from max_date
  group by dset;
 quit;

Upvotes: 1

Bendy
Bendy

Reputation: 3576

If they're all huge datasets (which I'm guessing they are) then you might be better off dropping a retain of the maximum date in the last processing datastep of each dataset you have to compare against - and then saving these to macro variables:

data table1(drop=maxdate) ;
  retain maxdate ;
  set _table1 end=eof ;
  *** other code here ;

  if date>maxdate then maxdate=date ;
  if eof then call symput('table1mdt',maxdate) ;
run ;

You can then write these out to a report such as ;

data _null_ ;
  put "Table1 max date= &table1mdt" ;
  put "Table2 max date= &table2mdt" ;
run ;

Upvotes: 0

defectus
defectus

Reputation: 1987

In plain SQL it would look like

select 'table1' table_name, max(date) max from lib.table1
union all
select 'table2' table_name, max(date) max from lib.table2
union all
select 'table3' table_name, max(date) max from lib.table3

Other options are stored procedures, in-line views and probably more.

Upvotes: 0

Related Questions