Hi @Hb21Pzn,   I think the performance question is still not answered. Why does a "delete from table"  takes so long for an RDBMS? First of all this is not a SAS issue. If you do this in Teradata admin or BTEQ it will take equally long. For starters it is because the RDBMS does journalling.  When a transaction fails it needs to be rolled back. Creating such a rollback log is very expensive and caqn potentially overflow buffers and bring a system down. Secondly an index may need rework for every deleted row. Also very expensive indeed.  Another cause can be the repeated validation of referential integrity (RI) constraints. If another table has a integrity dependency on your table every delete will require a validation of that constraint. These checks can be disabled or postponed but sooner or later they will cost you. Due to al this a "delete from table" is considered bad practice and will lead to a visit and a fatherly speach from your trusty DBA if you do it often (meaning more than once).   Oracle has a TRUNCATE function. TD has the ALL keyword: DELETE  FROM TABLE ALL;.  In TD the ALL keyword avoids transaction logging of each row's deletion. It wipes the cyliner headers and marks the tables as empty. It is called a fastpath delete. This means a brute force deletion of rows neglecting indexes and RI constraints. Messy but fast. Maybe just what you need   The DI Studio data loader has options for you to choose how to clear data from a table because of all this. Hope you can now  tell why.   So that's why dropping and re-creating the table altogether is often your best bet. Or maybe, if you use explicit passthrough, you can code the ALL keyword in your SQL.   Hope this helps, - Jan.   * Edited for typos. ... View more

Sorry D, but all I see is two steps that create a SAS data set. These do not result in a text file on the OS. That step is still missing. Please run the code I presented and tell me if that gives what you were looking for. The formats I see you using indeed result in proper spacing inside the variable as a result from the CAT() function. It is when writing that variable to an external file where you loose the trailing space unless you use a format in the PUT statement.   If that works consider implementing the logic of your SQL in that same datastep, thus eliminating a step and avoiding passes through your data.   - Jan. ... View more

Hi @dcortell,   We cannot see ther code you use to write to the file. But that's bound to be a datastep.   If you put the one var contyaining the whole record make certain you use a format. That will include the trailing whitespace.   data _null_; file ' outfile.txt'; set unified; put finals $129.; run; Hope this helps, - Jan. ... View more

So if I understand correctly you want to have two separate processes (SAS and Hive) for loading the data and still keep the keys in sync. I can think of one way but it depends on the existence of a primary key ijn your data (which is always a good idea). In the past I have used the MD5 function and fed it the variables that make up the key. MD5 should be portable enough that it would generate the same outcome for both Hive and SAS. If you are afraid of clashes (two different keys with the same MD5 summ, not unlikely) use the more modern SHA256 alternative. Thius yields a larger hash but the chances of a clash are very small indeed.   Of course any approach you like can be applied, even plain concatenation of the values, provided there is such a key available in your data.   Good luck, - Jan. ... View more

DI Studio has the Surrogate Key generator. Have a look at the doc. And if you add it to the SAS table which is subsequently loaded into hive, wouldn't they be automatically in sync?   Hope this helps, -Jan ... View more

On a side note you should keep an eye on the quoting of the password value. If passwords are complex (as they should be) they could easily contain characters that will throw off the execution of your code. Something like %NRBQUOTE() would make it more robust. So test your program with passwords that contain ampersands, punctuation, whitespace, single and double quotes as they are famous for causing havoc in SAS programs if not properly escaped. Just for fun try a password like the quote killer:   ;*';*";*/;   Good luck, - Jan. ... View more

Hi @GeorgeSAS,   There are a few approaches depending on the type of installation. If this is your nown machine/laptop then you could edit the SAS config file, located in <SAS Home>/SASFoundation/9.x/nls/en/sasv9.cfg. Add the line   -WORK "D:\mylib" Other options are: Start sas.exe with the -WORK option Edit the sasv9_usermods.cfg in the SAS Config directory (preferred if this is a multiuser environment)   So if you give us a bit more detail on your installation and configuration we may be able to narrow this down.    Also there is an article on support.sas com titled "Checking or Changing the SAS WORK" library".   Hope this helps, - Jan. ... View more