max in proc sql query for sql server

[AJ_Brien]

Hello,

I'm trying to get data from sql server table such that I need only the most recent observation- identified by max(dte).

I created the following code, it doesn't give an error, but it's producing results for all values of 'dte' and not just max(dte). I tried 'having' clause, but the 'group by' kept throwing me off. Would appreciate suggestions on what I should be changing in the following to get the desired result. Thank you!

proc sql;
	connect to SQLSVR (	user="%sysget(USER)" password="xx" dsn="xx" );
	create table table1 as select * from connection to SQLSVR
		(
			select 
					account , id, code, dte
			 from dbtable
  	    	where dte= (select max(dte) from dbtable)
			and code like '20%'
order by account;
);
	disconnect from SQLSVR;
quit;

[Tom]

Perhaps the table only has the most recent data?

Get a distribution on the values of DTE that are returned.

[AJ_Brien]

Thank you for your reply.
dte basically holds the dates, so instead of getting observations for the latest date, it's returning observations for all dates.
the source dbtable contains data from the last 10 years, so it's important to pull only the latest information.

[SASKiwi]

How about this?

	select account , id, code, dte
	from dbtable as A
        inner join 
        (select  account , id, code, max(dte) as max_dte
         from dbtable
         group by account , id, code
        ) as B
        on  A.account = B.account
        and A.id = B.id
        and A.code = B.code
        where A.code like '20%' and A.dte = B.max_dte              
        order by account;

[AJ_Brien]

thank you for the reply.

sadly it still gives all date observations instead of the max date observation.

[SASKiwi]

Because you are selecting by code (20%) I assumed you want the maximum date for each code combination, not on all records, so you will get multiple rows coming back, but just the latest for each code combination. If this is not what you want then please advise.

[AJ_Brien]

there are multiple records where code (20%) is satisfied. For eg, one single account has multiple records for various dates where code (20%) is satisfied. So instead of selecting all the records for that account, I only need the record for that account with the latest date where code (20%) is satisfied. Hope this helps.

[SASKiwi]

That is what I was trying to do. Try running just this then. You should only get one row for each value of account, id and code combination with the maximum date in each combination. Do you?

select  account , id, code, max(dte) as max_dte
         from dbtable
         where code like '20%'
         group by account , id, code
         order by account , id, code

[Tom]

---

@AJ_Brien wrote:
there are multiple records where code (20%) is satisfied. For eg, one single account has multiple records for various dates where code (20%) is satisfied. So instead of selecting all the records for that account, I only need the record for that account with the latest date where code (20%) is satisfied. Hope this helps.

---

That is a totally different query than what you originally posted. The original query is only looking for the latest date independent of account. Then further filtering those to just the ones that match the code restriction. 

Please post some example data, made up data is fine, and the expected output for that example.

[Patrick]

@AJ_Brien 

Below SQL works for a SAS table. There is no reason why this shouldn't yield the same result with SQL Server.

There is nothing in the SQL which SAS couldn't push to the database so eventually try the implicit SQL from below first.

data dbtable(drop=_:);
  do account=2 to 1 by -1;
    do code='2000','2010','1000';
      do _i=1 to 5;
        id+1000;
        dte=datetime()+id;
        output;
      end;
    end;
  end;

  format dte datetime20.;
  stop;
run;

options sastrace=',,,d' sastraceloc=saslog nostsuffix; 
proc sql;
  select 
    account , id, code, dte
  from dbtable
    where code like '20%'
      group by account
        having max(dte)=dte
  ;
quit;

[Tom]

Sounds like a bug in SQL Server.  Talk to the admins for your database to understand what syntx SQL server is expecting for that query.

You could possibly use SAS's macro language to help by first pulling the maximum date into a macro variable and then using that date in the query that pulls the actual data.  Note you will need to understand how to make the value of the macro variable look like how SQL Server handles dates. Perhaps something like this:

select cats("DATE('",put(datepart(max_dte),yymmdd10.),"')")
  into :max_date 
from connection to SQLSVR
  (select max(dte) as max_dte from dbtable)
;
create table table1 as 
select * from connection to SQLSVR
(
  select 
    account 
  , id
  , code
  , dte
  from dbtable
  where dte = &max_dte 
    and code like '20%'
  order by account
);

[ChrisNZ]

No too sure why you code doesn't work.

If you want to try another syntax, all three queries return the same result:

  select *
  from SASHELP.CLASS 
  where AGE = (select max(AGE) from SASHELP.CLASS)
  and SEX = 'M';
 
  select *
  from SASHELP.CLASS
  group by '1'
  having AGE = max(AGE)
     and SEX = 'M';

  select a.* 
  from SASHELP.CLASS a
      ,(select max(AGE) as MAX
        from SASHELP.CLASS)
  where AGE = MAX
    and SEX = 'M';

[s_lassen]

I understand that you want the latest record for each account, not just the records that are from the latest date in the whole table.

One possibility is to use SAS SQL instead, you just need to assign a SAS libname referencing the SQL server schema:

libname SQLSVR ODBC user="%sysget(USER)" password="xx" dsn="xx" schema=dbo;
proc sql;
	create table table1 as 
			select 
					account , id, code, dte
			 from SQLSVR.dbtable
  	    	where code like '20%'
       group by account
      having dte=max(dte)
order by account;
quit;

Another is to restructure the SQL Server query a bit:

proc sql;
	connect to SQLSVR (	user="%sysget(USER)" password="xx" dsn="xx" );
create table table1 as select * from connection to SQLSVR
   (select account , id, code, dte
     from dbtable join
       (select account,max(dte) from dbtable                        
  	    	where code like '20%'
        group by account) lastdate on
      dbtable.account=lastdate.account and dbtable.dte=lastdate.dte
order by account;
);
	disconnect from SQLSVR;
quit;