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;
... View more