topic Re: Create a loop in a SAS datatable / dataset and execute a store procedure each time in SAS Data Management

Create a loop in a SAS datatable / dataset and execute a store procedure each time

tassnh — Mon, 12 Sep 2022 08:26:42 GMT

Hi there,

I'm relatively new to SAS and I'd really appreciate your input into this. Any guidance would be deeply appreciated!

I have a few tables which I fill with table loaders. All of my data are from my SQL DB.

I also have a store procedure being executed at the back.

Now, the datatable I have created, leaves column 1 as empty since I don't get a value for that (yet).

I'm looking for a way looping into my datatable and each time have my store procedure being executed, in order to get a value and update my datatable with that value, at a specific part of my datatable (let's say 0, 1 field)(column 1). At the next loop my datatable should be updated at the field (1, 1), the one next at the (2, 1) and so on and so forth. How can I achieve that?

Please forgive my ignorance but I come from a VS environment and I thought this could be done easily but I can't seem to find a way out of this.

Thank you in advance for your assistance,

tassnh

Re: Create a loop in a SAS datatable / dataset and execute a store procedure each time

Patrick — Mon, 12 Sep 2022 10:52:57 GMT

Your question needs a few clarifications.

- Is that SAS9.4 or SAS Viya?

-"I have a few tables which I fill with table loaders": Are you using SAS DI Studio? Or what's this table loader?

"I'm looking for a way looping into my datatable and each time have my store procedure being executed, in order to get a value and update my datatable with that value, at a specific part of my datatable (let's say 0, 1 field)(column 1). At the next loop my datatable should be updated at the field (1, 1), the one next at the (2, 1) and so on and so forth. How can I achieve that?"

You're talking about a SQL Stored Procedure - right? Are you already able to call this procedure out of SAS?

I also don't understand what this stored procedure should be doing. You are loading some data from SAS into a SQL DB (is that MySQL or SQL Server?) and one of the columns in source is missing/empty and then you want to do "something"? What exactly should happen on the DB side. What value would you like to insert in case of the source column being empty?

Is your SQL Stored Procedure as such working? Can you share your code?

Re: Create a loop in a SAS datatable / dataset and execute a store procedure each time

tassnh — Mon, 12 Sep 2022 11:19:06 GMT

Hi Patrick and thank you for your reply!

As per your questions, please find my replies with red:

Your question needs a few clarifications.

- Is that SAS9.4 or SAS Viya? We are using SAS v9.4

-"I have a few tables which I fill with table loaders": Are you using SAS DI Studio? Yes

Or what's this table loader? Would you like to elaborate? I'm using table loader from Transformations --> Access --> Table Loader

You're talking about a SQL Stored Procedure - right? Yes

Are you already able to call this procedure out of SAS? Yes

I also don't understand what this stored procedure should be doing. This store procedure gives me the next system_id from another application in which I will be loading all the gathered information, once they are gathered from various other DBs. In other words, I NEED to have (in addition to the rest of the info gathered) the system_id that will be given by that app / DB and load the entire table into that DB.

You are loading some data from SAS into a SQL DB (is that MySQL or SQL Server?) (SQL Server) and one of the columns in source is missing/empty and then you want to do "something"? What exactly should happen on the DB side. As said before, I'm appending data into existing SQL table that also gets a system_id. That system_id has to be apprehended via that SP.

What value would you like to insert in case of the source column being empty? Integer

Is your SQL Stored Procedure as such working? Yes and already calling it both in SAS and in my other apps

Can you share your code? No, unfortunately can't do that. I'm sorry for the inconvenience

So, to sum up; I need to loop through my datatable and at each loop apprehend the system_id (which I will get from the SP) and append it into column 1. I hope I answered your questions. I'm at your disposal for further information.

Thanks again,

tassnh

Re: Create a loop in a SAS datatable / dataset and execute a store procedure each time

Patrick — Mon, 12 Sep 2022 11:58:16 GMT

Hmmm... I don't get 100% of your problem so eventually my answer not what you're asking for.

If this is about some auto-increment/identity column then what worked for me in the past:

- Exclude the column from the Proc Append

- Define the column as auto-increment/indentity column in the database

I'm not sure that I fully understand what your stored procedure is doing but just on a high-level I feel it's eventually easier to have the DB deal with SAS trying to insert blanks than SAS.

I've lately done similar things with Postgres where one can define as part of table DDL if an identity column can be populated from source if there is a value - or if not.

Another option is to use a trigger so whenever there is a missing from source it triggers a function that creates a value for insertion.

The "general" message is: Implement such specialties on the DB side and not on the SAS side. It's just easier.

Another reason to implement this on the DB side (if I understand right): You ensure that the column gets populated as it should without a dependency on the client side.

Re: Create a loop in a SAS datatable / dataset and execute a store procedure each time

Tom — Mon, 12 Sep 2022 13:11:10 GMT

It is not hard to generate code from data using a data step.

But you need to have an idea of

1) What code you need to generate. Show what SAS you need to run to execute the stored procedure and retrieve the value it generates.

2) How the code varies as the data changes. What part of the code changes? What is the name of the variable in the source data that can be used to generate the value?

Re: Create a loop in a SAS datatable / dataset and execute a store procedure each time

tassnh — Tue, 13 Sep 2022 05:32:12 GMT

Good morning Patrick,

Please find my replies in red:

Hmmm... I don't get 100% of your problem so eventually my answer not what you're asking for.

I'll do my best to explain my problem to the fullest

If this is about some auto-increment/identity column then what worked for me in the past:

Yes it is. The problem is that, this auto-generated, auto-increment number, is being used in other tables as well. This system_id is used in all tables that this app has and is also the key of the table. In addition to that, in most cases it is the foreign key of another table, in order to link / combine data

- Exclude the column from the Proc Append

- Define the column as auto-increment/indentity column in the database

Based on my previous statement (link between data) this is not feasible. I need to have that system_id prior to appending the retrieved data, in order to use it in another table and link the two table between them.

I'm not sure that I fully understand what your stored procedure is doing but just on a high-level I feel it's eventually easier to have the DB deal with SAS trying to insert blanks than SAS. My SP is basically auto-increamenting a number which gives me the next system_id. It's the easiest and most basic straight forward thing. It then updates another table which holds that system_id. So, in order to apprehend this system_id I need to loop through all my lines in my dt and assign that system_id to each line (on the first column). Then, I will be using that system_id of that line to link the specific value with another value on another table.

I've lately done similar things with Postgres where one can define as part of table DDL if an identity column can be populated from source if there is a value - or if not.

Another option is to use a trigger so whenever there is a missing from source it triggers a function that creates a value for insertion.

Using a demon is again not an option since, as described above, I need to have that system_id at hand before appending the data to my table, in order to link my data with my 2 tables

The "general" message is: Implement such specialties on the DB side and not on the SAS side. It's just easier.

In general, I have to be sure that, that system_id won't be given in another table at another value by the time I will be using it. I need to have that SP ran and assure that it won't be used by another call of that SP. In addition to that, I need to have that link between my 2 tables done, again before appending any data into my tables.

Another reason to implement this on the DB side (if I understand right): You ensure that the column gets populated as it should without a dependency on the client side.

I hope I have addressed all your questions. Please feel free to ask whatever else you might think you need to clarify.

Regards,

tassnh

Re: Create a loop in a SAS datatable / dataset and execute a store procedure each time

tassnh — Tue, 13 Sep 2022 05:42:14 GMT

Dear Tom,

Thank you for your reply.

Could you please elaborate on your answer? What is it exactly that you need me to post?

What I can do right now is better explain my problem, so please find my answers in red below:

It is not hard to generate code from data using a data step.

But you need to have an idea of

1) What code you need to generate. Show what SAS you need to run to execute the stored procedure and retrieve the value it generates.

I already have a dt that have apprehended from the various dbs that SAS connects to.

In that dt, I need to loop through all my lines and at each loop, execute a SP from my SQL, apprehend the result I get from that SP and assign it on the first column of each line on each loop. I can already execute the SP from outside SQL from within SAS. That is not my problem. My problem is that I don't know how to handle "loops" in SAS and within dts as is way different that the VS that I've been using so far.

2) How the code varies as the data changes. What part of the code changes? What is the name of the variable in the source data that can be used to generate the value? The code does not vary in any way as the data changes. I already have my dt with all data collected and, as stated above, I need to loop through all lines in that dt. So from line 0 of that dt until line "count(dt) - 1", I need to execute the SP from within SAS, retrieve my data and assign it to a specific field in my dt.

I don't know if what I described above makes sense but feel free to ask anything you think might help.

Thank you again for your reply,

Regards,

tassnh

Re: Create a loop in a SAS datatable / dataset and execute a store procedure each time

Tom — Tue, 13 Sep 2022 13:00:02 GMT

What does the SP actually DO? How do you CALL it? How do you get back the value you actually need to insert into your table? How would you return that value to SAS so you can then return it to the database.

You cannot start building the "loop" as you call it until you know what you are looping over. Right now you have not shown any working SAS code (or even any working SQL code in the dialect of whatever foreign database you are connecting to).

Re: Create a loop in a SAS datatable / dataset and execute a store procedure each time

tassnh — Wed, 14 Sep 2022 05:33:59 GMT

Good morning Tom,

Please find answers in red:

As described before, my SP gives an incremental number. I call it through Precode in Table Loader. I then select the new value from a table which holds that value in the SQL DB. I then need to insert it to my dt.

You cannot start building the "loop" as you call it until you know what you are looping over.

I think this is obvious. Why would you assume otherwise?

Right now you have not shown any working SAS code (or even any working SQL code in the dialect of whatever foreign database you are connecting to).

I am working for the IT dpt. of a bank corporation. We have a strict policy of sharing coding outside our organization and/or in forums. In a similar case a few years ago, a colleague was reprehended for sharing his code online. Sorry for the inconvenience but there is no alternative for that.

Thanks again,

tassnh

Re: Create a loop in a SAS datatable / dataset and execute a store procedure each time

Patrick — Wed, 14 Sep 2022 07:02:32 GMT

@tassnh The moment you share some code statements like below become much clearer

You don't need to share your actual code and also not all of it. Just take the relevant portion and "anonymize" it by changing things like schema name, table name and procedure name.

I've worked for many customers and as long as you don't share internal information none of them had ever a problem to share code snippets in a support forum.

If this ETL is for loading into a SAS Solution then please share the name of the solution. Some of us might have experience with it and already know how to load into such tables.

Are you by any chance loading into a dimensional model with a fact and dimension tables?

As I understand it you call the stored procedure once per row in the source table in SAS? For how many rows is that? I've seen something like that done in the past for daily high data volumes and then people were astonished that it performed badly...

Re: Create a loop in a SAS datatable / dataset and execute a store procedure each time

tassnh — Wed, 14 Sep 2022 07:34:18 GMT

Dear Patrick,

I'll try and do my best to give you an example that can be comprehended easily as well my task;

I have a dt with system_id, name, surname, tel, e-mail, dob, address etc.

All data are drawn from various tables from our dwh and another SQL DB of a Document Management System.

All data will be appended to that Document Management System's DB, in a table that handles customers. Let's call this customer_table

Customer_table gives a system_id for each entry. A system_id is also given for all the other tables that this app has, so, I need to get the system_id from the SP that handles the system_id. That system_id is kept in a different table which (after I run my SP) I will select it's value from that table (single table, single column, single row value). Let's call this system_id_table

Coming back to my dt; all data are available to me except the system_id (1st column).

All lines are new entries of customers inserted into the customer_table

In order to append this dt into my customer_table, I first need to assure that the table is complete and includes the system_id. So, in order to get the system_id, I need to execute the SP once for each line, select the value from the system_id_table and update my dt with that value. This has to loop for all the entries in my dt. This procedure will not loop for more than ~30 times. To my knowledge their won't be more than 30 customers on daily basis so, it will have to loop from line 0 till line max -1.

Does that make sense? I hope it helps you make a better understanding of my situation.

Please fell free to ask me anything you might think will help you clear things better out.

Thanks again,

tassnh

Re: Create a loop in a SAS datatable / dataset and execute a store procedure each time

Patrick — Wed, 14 Sep 2022 09:57:07 GMT

From what you describe I assume your "system_id_table" is actually a sequence object as documented here
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver16

You then got a stored procedure which calls this sequence object and returns the next increment.

What's missing and really important for us to understand is how you call this stored procedure out of SAS and especially how you get the value back into SAS.

Normally it's a FUNCTION that returns a single value where else a Procedure is a whole "program" and can return a whole result set. So not sure if you're using the right terminology.

SQL Stored Procedure: https://www.w3schools.com/sql/sql_stored_procedures.asp

As soon as we would see the code that's already working (just change the names) and which loads a single new id into SAS, a lot of ambiguity in your description could be resolved.

In regards of "looping" in SAS:
The SAS data step is in a way nothing else than a loop which processes one row at a time. The data step will iterate as many times as there are rows in your table.

You really need to share some snippet of your code as else there is just way too much guesswork.

Once you've got a set of IDs on the SAS side things will be simple.

And last but not least:

If you don't find a way to share the relevant code snippets then I'm not sure that "we" can really help you.

Re: Create a loop in a SAS datatable / dataset and execute a store procedure each time

tassnh — Wed, 14 Sep 2022 10:45:13 GMT

Patrick, please find my answers in red:

Precisely

You then got a stored procedure which calls this sequence object and returns the next increment.

Yes, exactly

What's missing and really important for us to understand is how you call this stored procedure out of SAS and especially how you get the value back into SAS.

When you call this procedure, the value gets updated and then stored again in the table I mentioned earlier (see my previous reply) (system_id_table). The only thing left to do is to read (select) the value in that table)

Normally it's a FUNCTION that returns a single value where else a Procedure is a whole "program" and can return a whole result set. So not sure if you're using the right terminology.It's the correct one 🙂 I've been working on SQL for more than 15 years

SQL Stored Procedure: https://www.w3schools.com/sql/sql_stored_procedures.asp

As soon as we would see the code that's already working (just change the names) and which loads a single new id into SAS, a lot of ambiguity in your description could be resolved. So, you want to see how I execute the SP or how I get the new value? As stated earlier, I just select my new value after I executed my SP

In regards of "looping" in SAS:
The SAS data step is in a way nothing else than a loop which processes one row at a time. The data step will iterate as many times as there are rows in your table.

Is there an example I can have to follow? Can you share something with me?

You really need to share some snippet of your code as else there is just way too much guesswork.

I totally understand and I 'll see if I can do that, altering my code. Had it not been sensitive information, I wouldn't hesitate sharing the minute you asked! Just tell me what exactly you need me to post. Calling the SP, my solution on SAS (which is huge), selecting the value of the executed SP?

The one problem that needs resolution is creation of a set of new ID's on the SQL server side and retrieve this set in SAS. Ideally your stored procedure (if it's one) allows for parameter passing where you can define how many new IDs you need - and then it's a single call. It returns a single value at a time, thus the loop. Had it been multiple values, I 'd have called for that range, get them in an array and apply them into my dt, but I'm afraid it's not.

Once you've got a set of IDs on the SAS side things will be simple.

We thought the same thing 🙂

And last but not least:

If you don't find a way to share the relevant code snippets then I'm not sure that "we" can really help you.

I stated above, please clarify me the things you need me to share and I'll try sharing them

Regards,

tassnh

Re: Create a loop in a SAS datatable / dataset and execute a store procedure each time

Tom — Wed, 14 Sep 2022 13:11:21 GMT

That system_id is kept in a different table which (after I run my SP) I will select it's value from that table (single table, single column, single row value). Let's call this system_id_table

So that might be enough to make some pseudo code to show to process.

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.

Let's assume you already have a connection to the database defined by the libref MYDB.

So your single instance program might look like:

proc sql;
connect using mydb ;
execute by mydb
  ( run_my_stored_procedure )
;
create table new_id as select * from connection to mydb
(select system_id from system_id_table)
;
quit;
data for_upload ;
  merge new_id new_data;
run;
proc append base=mydb.target_table data=for_upload;
run;

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.

I also do not see how the source dataset you wanted to loop over applies to the process.

Is it just that you need to call the stored process once for every observation in the dataset?

Or are there values in the source dataset that need to serve as inputs to the stored process?

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.

So if the process needs to take the value of NAME from the input dataset, let's call the dataset HAVE, then macro a macro that has a NAME parameter and you can then call it once per observation.

data _null_;
  set have ;
  call execute(cats('%nrstr(%mymacro)(name=',quote(trim(name),"'"),')'));
run;

Re: Create a loop in a SAS datatable / dataset and execute a store procedure each time

Patrick — Thu, 15 Sep 2022 04:54:30 GMT

@tassnh

Given your many years of SQL experience would you be able to write this in SQL directly via some client (select * from .....). If you can write such code then it's very simple to execute it out-of-SAS in an explicit pass-through SQL block and then just retrieve the result set back to SAS.

Both please. How you call the SP out of SAS to increment the value and then get this value back into SAS.

Re: Create a loop in a SAS datatable / dataset and execute a store procedure each time

tassnh — Thu, 15 Sep 2022 04:47:38 GMT

Good morning Patrick,

That statement of mine was an indication for my intentions and not my difficulties.

Given your many years of SQL experience would you be able to write this in SQL directly via some client (select * from .....).

Are you asking me (in this part) if I will get back the result I need if I select it from my table. If so, the answer is yes.

In general practice and in my (VS) apps, if and when I need to get the value of the system_id, this is exactly what I do. But VS is way more different than DI and way more "compatible" to MSSQL. In addition, the highlight you posted was my intention to explain the procedure and what is left to do after going through the previous steps. Actually, this is exactly what needs to be done. Select the value in the table and this is the system_id needed. Then, the SP is executed and the value in the table gets updated; and again, you select the new value as your new system_id.

My main problem (and the problem stated from the very beginning) is the loop in my dt. I need to know the exact way to loop through my dt, apprehend my system_id from my table and assign it to the first field (each time) of my _dt. This must loop for as many times as my rows in my dt.

I deeply appreciate your response and I do hope this clears thing better.

Last but not least; If you can write such code then it's very simple to execute it out-of-SAS in an explicit pass-through SQL block and then just retrieve the result set back to SAS.

Can you please elaborate on that a bit? What do you mean exactly when you say "execute it out-of-SAS in an explicit pass-through SQL block"? Execute it in SQL Management Studio? Somewhere intermediate? Please keep in mind that, my experience with DI is not huge.

Kind regards,

tassnh

Re: Create a loop in a SAS datatable / dataset and execute a store procedure each time

Patrick — Thu, 15 Sep 2022 04:59:17 GMT

If you can't share any code for us to understand where you are at right now then it's really hard to provide concrete advice.

Having said that: It's going to be something pretty close to what @Tom just shared as code. ...but again: You need to talk "code" with us for us to be able to amend/extend your code to what you need.

Re: Create a loop in a SAS datatable / dataset and execute a store procedure each time

tassnh — Thu, 15 Sep 2022 05:27:14 GMT

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

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

Re: Create a loop in a SAS datatable / dataset and execute a store procedure each time

tassnh — Thu, 15 Sep 2022 05:30:28 GMT

Dear Patrick, my answers in red:

If you can't share any code for us to understand where you are at right now then it's really hard to provide concrete advice.

Having said that: It's going to be something pretty close to what @Tom just shared as code. ...but again: You need to talk "code" with us for us to be able to amend/extend your code to what you need.

ok, could you indicate me what part of the coding you need me to post?

My SP? My select from my db/table? The SP call?

Please check my reply to Tom. Maybe that would sort things out a bit.

Kind regards,

tassnh

Re: Create a loop in a SAS datatable / dataset and execute a store procedure each time

Tom — Thu, 15 Sep 2022 14:36:42 GMT

Trying to identify the forest from the bumps on the leaves of the trees your answer is showing:

It sounds like you have a dataset that is a list of customers. The dataset has variables like

ID, NAME, DOB, ADDRESS, PHONE

You now want to add a new variable, SYSTEM_ID, by using the stored process in your foreign database. Each procedure call only produces one new value. So if you have 100 customers in your dataset you will want to call the procedure 100 times.

One way to do that is to call the procedure 100 times and extract the new system_id values and save them into a SAS dataset. Then you just need to combine the dataset with the list of 100 customers with the dataset with 100 new system_id values.

Example (wallpaper code):

* Make an empty dataset for storing new system_id values ;
data new_system_ids;
  length system_id 8;
  stop;
run;
proc sql;
  CONNECT TO ODBC (...);
  EXECUTE BY ODBC ( EXEC [H].[dbo].[sp_nkey] ) ;
  insert into new_system_ids(system_id)
    select system_id from connection to odbc
     (select system_id from system_id)
  ;
  EXECUTE BY ODBC ( EXEC [H].[dbo].[sp_nkey] ) ;
  insert into new_system_ids(system_id)
    select system_id from connection to odbc
     (select system_id from system_id)
  ;
  EXECUTE BY ODBC ( EXEC [H].[dbo].[sp_nkey] ) ;
  insert into new_system_ids(system_id)
    select system_id from connection to odbc
     (select system_id from system_id)
  ;
...
disconnect from odbc;
quit;
data want ;
   set have;
   set new_system_ids;
run;

Now to generate that "wallpaper" code you could just count how many ids you need by counting how many customers are in your HAVE dataset. You could then use code generation to generate code. SAS macro language is one way to generate code.

%macro system_ids(n);
%local i;
%do i=1 to &n ;
  EXECUTE BY ODBC ( EXEC [H].[dbo].[sp_nkey] ) ;
  insert into new_system_ids(system_id)
    select system_id from connection to odbc
     (select system_id from system_id)
  ;
%end;
%mend;

data _null_;
  call symputx('nobs',nobs);
  stop;
  set have nobs=nobs;
run;

* Make an empty dataset for storing new system_id values ;
data new_system_ids;
  length system_id 8;
  stop;
run;
proc sql;
  CONNECT TO ODBC (...);
  %system_ids(&nobs);
  disconnect from odbc;
quit;
data want ;
   set have;
   set new_system_ids;
run;

NOTE: If the value is a BIGINT in your foreign database you might need to store it as a string in your SAS dataset since SAS can only represent integers with 15 decimal digits exactly. In that case in the passthru query to pull the new value of system_id cast it as a string first so that you can preserve the uniqueness of the value.

PS How can the system you described actually work? What happens if two users call the stored procedure at almost the exact same time so that it executes twice before the query to receive the number can run. So when they go to get the value both will take the same value. This is not how this type of autoincrement features work in other datasets I have seen. For example in Oracle it is a FUNCTION that you call to get the next value. So it can be returned as part of a query. Then there is no window of time between generating the value and retrieving it that could cause the race condition I just described.