Last_Insert_id() from Proc SQL?

[deleted_user]

Hi all,

I'm looking for a way to store the last auto id that was inserted into a mysql database.

Basically, I need to insert a new observation into a database using SAS. The obervation will get an auto id once inserted. I required this auto id for inserting another observation into a different table.

I'm just wondering, I'm guessing I can only use

select LAST_INSERT_ID() as id

when using pass-thru sql, how am I to get this value out into the local sas when my data to be inserted is stored locally. I've got a local table in work, that I need to insert into a remote table, but I will required the auto id from one insert instance to be used for the next insert instance.

Or is it possible to use pass-thru sql to read from a local table and insert data into remote table?

Thanks for any help.

[GertNissen]

If you don't find another solution, then try this rather ugly hack 🙂

PROC SQL;

connect to ODBC as mssql(noprompt="DRIVER=SQL Server;Server=SQLserver;DATABASE=SQLdatabase");

execute ( INSERT INTO dbo.DWtable (status) VALUES ('text') ) by mssql;

execute ( create table #temp (TempTableField int)) by mssql;

execute ( INSERT INTO #temp SELECT IDENT_CURRENT('dbo.DWtable')) by mssql;

create table temp as select * from connection to mssql (select max(TempTableField) as max from #temp);

disconnect from mssql;

QUIT;