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!
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;
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;
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?
Is it possible to look if a record exists (please refer my original post)?
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'
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;
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.