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

[tassnh]

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

[Patrick]

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?

[tassnh]

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

"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? 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

[Patrick]

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.

[tassnh]

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

[Tom]

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?

[tassnh]

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

[Tom]

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).

[tassnh]

Good morning Tom,

Please find answers in red:

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.

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

[Patrick]

@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...

[tassnh]

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

[Patrick]

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-v...

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.

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. 

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.

[tassnh]

Patrick, please find my answers in red:

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-v...

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

[Patrick]

@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.

[tassnh]

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