BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
M786
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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

7 REPLIES 7
SASKiwi
PROC Star

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;

M786
Calcite | Level 5

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?

M786
Calcite | Level 5

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

SASKiwi
PROC Star

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'

M786
Calcite | Level 5

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;

SASKiwi
PROC Star

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.

M786
Calcite | Level 5


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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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