Good morning Tom, I will try to answer in detail and be as thorough as I can. Please find my replies in red: Sounds like you are doing something like this: 1) Connect to database. 2) run procedure in database. 3) query table in database. 4) upload data to database. You might want to revert 2 and 3 between one each other. First I select the value of the table, then run my SP (in which the value gets updated) and get the value of the table again. This has to loop for as many times as my rows. Each time I select the value I get a new system_id. At the end, I need to run it once more so that the app will use it for its own purposes. Between 3 and 4 there is another (if not more) step. I need to update the value I got from my table in my dt (in DI), at the first field. As stated before, this has to loop for as many times as my line in my dt. Then comes step "4", in which I append my dt in my other table (Document Management Table). Let's assume you already have a connection to the database defined by the libref MYDB. I do, with a specific username / password at a specific instance / db Are you asking for this?: Proc sql; CONNECT TO ODBC(DATAsrc=H USER=sas_h PASSWORD="{SAS102}xxxxxxxxxxxxxxxxxxxxxx" ); EXECUTE ( EXEC [H].[dbo].[sp_next_system_id] ) by ODBC; DISCONNECT FROM ODBC; quit; What we cannot tell from your description is exactly what it is you want to pull form SYSTEM_ID_TABLE, how to find the "new" id that the process generated, etc etc. As stated before, this is a single column, single row table, which holds the next system_id that will be given by the system (integer). So, in other words, I want to select the value (let's say that the value is 1000) and assign this value to my first field in my dt. I will then need to execute my SP, the SP will increment the value of the system_id table by 1 (so the new value in the table will be 1001) and select it again (for as many times as my rows in my dt) and reassign it to my 2nd line, 1st field of my dt. For instance: Proc sql; CONNECT TO ODBC(DATAsrc=H USER=sas_h PASSWORD="{SAS102}xxxxxxxxxxxxxxxxxxxxxx" ); EXECUTE ( EXEC [H].[dbo].[sp_nkey] ) by ODBC; DISCONNECT FROM ODBC; quit; I also do not see how the source dataset you wanted to loop over applies to the process. This is not a source ds. This is a dt / ds created (generated) from various selections both from DWH and my DB in SQL. From all fields retrieved, the only thing left behind is the system_id which (since this dt will be appended to a production app) need to be given to the row at the very last second and is a new value (not an existing one). To describe it in more detail; I am constructing let's say a customer, based on his ID. I need to get his name from the table that holds the names, based on his ID. I need to get his date of birts from the table that holds the dates of birth, based on his ID. I need to get his address from the table that holds the addresses, based on his ID. I need to get his teleplhone from the table that holds the telephone numbers, based on his ID I construct a table (and map it on another table which is a whole different story). I also append an empty column on this table. Now, for each line (which is for each client), I need to assign a system_id, based on my app (document management app). So, I need to loop through my rows, select the value of system_id from system_id table, execute the SP to increment the value of the system_id by 1 and go to the next line The only thing missing is step "7". Is it just that you need to call the stored process once for every observation in the dataset? Yes and get the value from the system_id table (as explained in detail above) Or are there values in the source dataset that need to serve as inputs to the stored process? No. Just the 1st one In general a simply way to repeat multiple steps for every observation in a dataset is to make a macro that does the steps that takes as input the information that is the dataset. Then just generate one call to the macro for every observation in the dataset. Could you please elaborate a bit in more detail? Please bear in mind that I’m not that experienced in DI. Thank you for all the effort you are putting into this. Please let me know if there is something else you need me to clarify or coding you might think it will help more. Kind regards, tassnh
... View more