DATA Step, Macro, Functions and more

SAS tigger that pulls the data from SQl based on criteria

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

SAS tigger that pulls the data from SQl based on criteria

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!


Accepted Solutions
Solution
‎06-16-2015 04:13 PM
Super User
Posts: 3,110

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

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;

View solution in original post


All Replies
Solution
‎06-16-2015 04:13 PM
Super User
Posts: 3,110

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

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;

Occasional Contributor
Posts: 5

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

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?

Occasional Contributor
Posts: 5

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

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

Super User
Posts: 3,110

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

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'

Occasional Contributor
Posts: 5

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

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;

Super User
Posts: 3,110

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

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.

Occasional Contributor
Posts: 5

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


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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 313 views
  • 2 likes
  • 2 in conversation