Reading values from a SQL table

[JonathanSamuel]

Hi All

I'm trying to automate an Text message reply service where potential customers respond to a marketing Text mesage with a  'Yes' (or various versions of it considering typo's etc).

My idea is to read the replies in the table from 12:00:01 until 12:00:30 and append the results to 2 separate temporary tables, exclude duplicate replies from the same number and where the reply is 'yes' (or various versions) then append to temp table table 1 and all other replies to temp table 2.

At 12:00:31 read from the next value onward for the next 30 seconds worth of replies and loop through the above process.

The 'Yes' responses are due to get a 'Welcome' text message as soon as possible

I've attached a screen-grab of what the reply table looks like in SQL

The idea is to fully automate the process which is now manually done. Im currently using SAS EGuide V 6.1 (64-bit)

Please give me some ideas?

[ballardw]

By "read values from SQL" do you mean that the data is stored is some DBMS such as Oracle Sql Server, MySQL or similar?

SAS has available as part of the SAS/ACCESS packages tools to use many of the major databases and may well be the place to start though likely to require addition to your SAS license. ODBC may be possible as well but more details.

I think you may need to provide more details of what actually is "looped through" and what the actual result for some example data would look like. Since none of your example data has any values anywhere near 12:00:31 it might help to recast the question/description in terms of values, such as either UserId or CustomerID or other variables of interest and walk us through some use cases.

[JonathanSamuel]

Hi Ballard

Firstly, thanks for your reply..

I manage a digital marketing platform. At the moment, I use SAS CI Studio to send bulk SMS's (Text messages) to a pre-defined list of customers that are uniquely identified by the Customer_ID.

At the moment I manually query a SQL Server table (SMS reply table) paste the result into an excel spreadsheet and sort through the responses to isolate the customers that indicated they want to take the product offered. This subset of customers are then loaded into a mainframe architecture which we use as our 'billing' system.

What i would like to do is automate the checking of the reply table, which i currently do at perhaps 5 hour intervals daily. Manually recording the last received datetime stamp and using that as my starting point for the next time I check through the replies so as not to select a duplicate customer.

My idea is to somehow automate the monitoring of the reply table and select appropriate responses and output the selection in the required format to a desired location for automatic absorption by the billing system (which at this moment is a .txt file)

The values I refereed to in my first piece (12:00:00 to 12:00:30) was an example of the time stamp, meaning from 12h00 to say 12h05 store those responses and then start reading replies again from 12h05 and 1 second?

Hope this makes sense?