Updatingor replacing an existing table in a ODBC database

[edison83]

Hi folks, i am strugling to update one table that i am currently being able to import into the database that i am working.

I am pretty naive in SAS programming, am i missing something?

Just so you guys can understand, i am being able to connect to this database from other company, import some data files, work on these files doing data management, and finally i am being able to import it back.

The thing is, now that i have the script built to manage the data, they will update their database every week, therefore i want to update my final ile that i am importing back to the database weekly as well.

however, when i try to do this SAS shows a error message saying that the table is already there.

If i use a different name i can import a new table without problem, i just want to avoid exporting a new table every week with a differentt name ( it will be 52 different tables after 1 year).

What do you guys think, is there any easy way to update the whole table ( it's big...almost 4000 rows and 300 columns), or is there any way to exclude the old one and import a new one with the same name?

Sorry for my dumbs questions...

i have the code below that i am using to import my final table back into the database.

/* CONNECTION TO THE LIBRARY to work on the data*/

libname mariadb ODBC dsn=mariadb user="x_x" password=y_y;

/* exporting final file back into the library*/

data set mariadb.Tablefinal; set work.Table1;

run;

[ballardw]

Not really an expert on ODBC but I would investigate all the settings on your ODBC connection.

Different connections have different types of abilities and some may just not have a "replace" ability. Others may require that the ODBC channel have that ability set for you (permissions) or even enabled on the server side.

I might suggest that you keep start and end versions of the data locally for a while, or maybe permanently, for audit or to show that the result was proper given the starting data. The weekly file thing isn't much of an issue. You can search this forum for multiple instances of generating file names based on date rules.

[SASKiwi · Accepted solution]

What ODBC database are you connecting to?

If you simply want to replace the existing table with a new version then you first have to delete all of the existing rows. I find this method works well:

libname mariadb ODBC dsn=mariadb user="x_x" password=y_y;

proc sql;
   connect to odbc using mariadb; 
   execute (delete from Tablefinal) by ODBC;
   disconnect from ODBC;
quit;

Once all rows are removed then using an APPEND is easy:

proc datasets library = mariadb nolist;
  append base = Tablefinal data = work.Table1;
run;
quit;

[edison83]

Thanks a lot, that really worked here!

Just a quick question...now i am having some issues in different formats and lengths for multiple columns!

Is there any simple way to just completely delete the previous Tablefinal and include a new one with the same name?

[SASKiwi]

It isn't a good idea to keep changing a table definition constantly based on the table you are appending, at least for changing column lengths. Occasionally adding or deleting columns is OK. You should decide what your permanent definition should be then ensure your APPEND table matches that.

In my experience most database administrators wont allow users to change permanent table definitions anyway, you have to raise a change request with them to do it.