topic SAS tigger that pulls the data from SQl based on criteria in SAS Programming

SAS tigger that pulls the data from SQl based on criteria

M786 — Tue, 16 Jun 2015 14:57:49 GMT

Hi all

I need a SAS Program(Which will be scheduled) that hooks into SQL and look for existence of few records in a table (ex: country=UK date=today and phase=success).
If SAS program finds success as a value for todays date along with other criteria then it should pull the records from other table.
If the criteria is not met it should loop back and check again until it finds Phase = success. Along with that it should also send an email saying that the process is successful.
Hope my question is clear

THANKS IN ADVANCE!

Re: SAS tigger that pulls the data from SQl based on criteria

SASKiwi — Tue, 16 Jun 2015 20:13:03 GMT

Yes this is indeed possible. I've done something very similar except in my case just counting table rows. Building a macro is the way to go. You can use the SLEEP function in macro to create a delay loop.

Here is a code skeleton you can use to get you started - it will require customisation to your requirements:

%macro Loop;

%let _Row_Count = 0;

%let Loop_Count = 0;

%let Wait_Minutes = 10;

%let Loop_Max_Times = 5;

%do %until (&_Row_Count gt 0 or (&Loop_Count ge &Loop_Max_Times));

%let Loop_Count = %eval(&Loop_Count + 1);

%let Time_Now = %sysfunc(datetime(), datetime20.);

proc sql noprint;

connect to odbc (noprompt = "YourConnectionString");

select strip(put(Row_Count, comma12.))

into :_Row_Count

from connection to odbc

(SELECT count(*) as Row_Count

FROM TableName

);

disconnect from odbc;

quit;

%if &Loop_Max_Times gt 1 and &Wait_Minutes gt 0 and (&_Row_Count = 0) %then

%let slept = %sysfunc(sleep(&Wait_Minutes, 60));

%end;

%mend Loop;

Re: SAS tigger that pulls the data from SQl based on criteria

M786 — Wed, 17 Jun 2015 08:10:29 GMT

Hi SASKiwi,

Thanks for your suggestion. How do I code to search if a record exists(long with the criteria above) in the sql table?

Re: SAS tigger that pulls the data from SQl based on criteria

M786 — Wed, 17 Jun 2015 10:04:11 GMT

Is it possible to look if a record exists (please refer my original post)?

Re: SAS tigger that pulls the data from SQl based on criteria

SASKiwi — Wed, 17 Jun 2015 20:00:23 GMT

You could simply count up the number of rows that satisfies your selection criteria. If the row count is 0 then there is no data for your selection.

SELECT count(*) as Row_Count

from TableName

where country = 'UK' and date = today() and phase = 'success'

Re: SAS tigger that pulls the data from SQl based on criteria

M786 — Thu, 18 Jun 2015 11:35:14 GMT

Hi SASKiwi, Thanks again for your valuable suggestions.

Below is the code I have modified to do the following:

If Row_count gt 0 it should extract a table and send a email otherwise wait for 10min and check again.

If Row_count >0(which is true in my case) a table should be extracted from sql. But when I run the below code the table is extracted with or without meeting the condition(Row_count gt 0) and the code does not stop running.
What am I doing wrong?

%macro Loop;
%let Row_count      = 0;
%let yesterday      = '2015-06-16';
%let today          = %sysfunc(today(),yymmdd10.);
%let Loop_Count     = 0;
%let Wait_Minutes   = 10;

%do %until (&Row_count gt 0);
%let Time_Now = %sysfunc(datetime(), datetime20.);

proc sql exec;
connect to odbc (&connectA);

SELECT count(*) into : Row_count
FROM connection to odbc
(
SELECT * FROM [xxxx].[xxx].[xxxx] A WITH (NOLOCK)
        WHERE Category          = 'xxxx'
        AND ProcessName        = 'xxxxx'
         AND DBName            = 'xxxxx'
        AND ObjectName         = 'xxxxxxx'
        AND Phase              = 'SUCCESS'

AND StartDate = &yesterday

);
disconnect from odbc;
quit;

%if &Wait_Minutes gt 0 %then
%let slept = %sysfunc(sleep(&Wait_Minutes, 60));
%end;

proc sql exec;
connect to odbc (&connectA);
create table data as
select * from connection to odbc
(

SELECT * FROM [xxxxxx].[xxxx].[exxxxxx] A WITH (NOLOCK)
);
disconnect from odbc;
quit;

%mend Loop;

%loop;

Re: SAS tigger that pulls the data from SQl based on criteria

SASKiwi — Thu, 18 Jun 2015 19:40:02 GMT

I would do your row count like this:

proc sql exec;

connect to odbc (&connectA);

SELECT row_count into : Row_count
FROM connection to odbc
(
SELECT count(*) as row_count FROM [xxxx].[xxx].[xxxx] A WITH (NOLOCK)
        WHERE Category          = 'xxxx'
        AND ProcessName        = 'xxxxx'
         AND DBName            = 'xxxxx'
        AND ObjectName         = 'xxxxxxx'
        AND Phase              = 'SUCCESS'

AND StartDate = &yesterday

);
disconnect from odbc;
quit;

Also you don't have any timeout logic in your code hence it keeps running. Add the timeout functionality from my original example. For testing if the timeout works then set it to timeout after 2 loops, for example.

You should test that the row count logic is working properly for when there are both 0 rows and non-zero rows - do this outside of the macro to keep things simple.

Re: SAS tigger that pulls the data from SQl based on criteria

M786 — Fri, 19 Jun 2015 07:15:49 GMT

The reason I have removed the timeout is I will be scheduling this code and there is an option that stops the code running if it runs more than some time.