i am using SAS Data Integration Studio 4.4. I have a table filled with values there, which i want to export into an oracle database. The connection to the database and the exporting itself is no problem. But the column "ID" should count up with every new row. How to solve this problem on SAS side?
I can't add an autoincrement on the database for the ID for certain reasons.
Any suggestions? Thanks in advance.
In a code editor, you should be able to do something approximately like this. These assume you already have oralib defined as your connection to oracle (in a libname statement or DIS's equivalent).
* Get last ID in oracle into a macro variable;
proc sql;
select max(id) into :maxID from oralib.oratable; *make these the real oracle libname and oracle table name;
quit;
*use it;
data append_to_oracle;
set your_data_set; *whatever dataset contains the data you want to insert into oracle;
ID = _n_+&maxID.; *&maxID. contains the highest ID in oracle, so this will add 1 or more to it;
run;
*you could make this a view to save processing time if that is important - like so;
*replace data line with;
*data append_to_oracle/view=append_to_oracle;
*append to oracle however you currently append;
_N_ is the data step loop counter and acts similar to an autoincremented column when doing simple datasteps (always starting at 1).
If your oracle table is large, such that the max(id) query might take a while, you might consider saving the max ID in a text file or similar locally so you don't have to keep querying it.