mitoKon mitoKon - 6 months ago 28
SQL Question

SAS - Insert statement into existing SQL table

Here is my code:

PROC SQL;
connect to odbc (dsn=ODC uid=sa pwd=XXXXX);
EXECUTE ( INSERT INTO dbo.tblDLA_Backup SELECT * FROM &dlafile.) BY ODBC;
disconnect from odbc;
quit;


Im getting this error


ERROR: CLI execute error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name 'work.dlabackup'.


If i do this:

proc sql;
connect to odbc (dsn=ODC uid=sa pwd=XXXXX);


insert into tblDLA_Backup SELECT * FROM WORK.DLABACKUP;

disconnect from odbc;


quit;

I get this error:


ERROR: File WORK.TBLDLA_BACKUP.DATA does not exist.


Why is it that I can't reference my SAS dataset and just insert? it should be simple as that..

Answer

The first error occurs because you are executing an SQL instruction on SQL Sever, and not locally. And since this instruction contains a reference to your local SAS dataset, an error occurs because SQL server think it is a table in its own database.

You take a wrong approach on that one.

Your second approach is correct because you are executing the SQL in SAS, which both knows the local table and the SQL server tables. And it is syntaxically valid at first glance.

The error is clear: SAS doesn't found the local dataset WORK.TBLDLA_BACKUP

Thus, verify if this dataset exists and is not corrupted:

in your explorer window, click on Libraries, then Work, and verify if TBLDLA_BACKUP is there, and if yes open it and check if you see your data.

I can't say more at this point, but you should hopefully discover something.

Comments