Schedule a sas program to trigger shell script

[visa]

%macro Date_test();

PROC SQL;
CONNECT TO TERADATA(details) ;
CREATE TABLE MAX01 AS
SELECT * FROM CONNECTION TO TERADATA
(
SELECT max(date_col) as d, current_date-1 as curr_date from table;
);
QUIT;



PROC SQL;
SELECT d format=YYMMDD10. ,curr_date format=YYMMDD10. into: max_dt, : cur_dt  FROM MAX01;
quit;

%put &max_dt &cur_dt;


%mend Date_test;


data _null_;

do i=1 to 5 while (i lt 5);

     if &max_dt. = &cur_dt. then do;
        call system('Sample.ksh');
         i+10;
	 end;
else do;
	 x = sleep(3600,1);
	 call execute ('%Date_test();');
     end;
end;

run;

I am new to SAS.  The intention of the program is to trigger a shell script if yesterday's data load is complete. If not sleep for an hour until 4 hours to check again and again to run the shell script.

First part of the program is a macro to check for the max date and current date -1 from database and store it in a macro variable. In a ideal scenario these both should be equal. For eg, if I run it today (27th Feb), it should be 26th Feb for both.

Second part is Data _null_ step to check the condition in IF part and trigger the shell script. Else, sleep for 1 hour and then run the macro again and begin with Do loop.

I am unsure about the following:

1.Here, how do I pass the max_dt and curr_dt back to Data _null_ step?

2. The shell script is not getting triggered using Call system command. How do I make this work? I have tried using sysexec and Filename commands already.

3. Any loopholes that I am missing here..

Thanks a lot for your help!

[Kurt_Bremser]

These macro references:

if &max_dt. = &cur_dt. then do;

will be resolved exactly once, when the data step is compiled, and will never change during data step execution.

Your attempt to call SQL inside a data step will not work, the code called with call execute will only start to run once the data step has finished.

Why don't you write a simple SAS program that checks a dataset and exits with a given return code, depending on your condition, and evaluate that in a shell script?

[visa]

@Kurt_Bremser Thanks for your response. Would this make a difference? Assigning a global variable and calling the macro at the beginning of DO loop? Also can you please expand on what you're suggesting below?

%global max_dt cur_dt;

%macro Date_test();

PROC SQL;
CONNECT TO TERADATA(details) ;
CREATE TABLE MAX01 AS
SELECT * FROM CONNECTION TO TERADATA
(
SELECT max(date_col) as d, current_date-1 as curr_date from table;
);
QUIT;



PROC SQL;
SELECT d format=YYMMDD10. ,curr_date format=YYMMDD10. into: max_dt, : cur_dt  FROM MAX01;
quit;


%put &max_dt &cur_dt;
%let max_dt= &max_dt.;
%let cur_dt= &cur_dt.;


%mend Date_test;

data _null_;

do i=1 to 5 while (i lt 5);
call execute ('%Date_test();');
     if max_dt = cur_dt then do;
        call system('Sample.ksh');
         i+10;
	 end;
else 
	x = sleep(3600,1);
end;
run;

[Kurt_Bremser]

You CANNOT(!) influence data step variables with code in a call execute, as that code will run AFTER(!) the current data step has finished, and won't do anything before that.

Go back to square one, and define your problem/task in plain language. Then select the proper tools (which IMO won't include SAS for most of the work), then start coding.

[Satish_Parida]

The issue is you did not call the macro before executing data step.

And your approach is wrong.  I have made few corrections. Please close a post before re-posting.

Here is a fix. And also keep in mind the things  @SASJedi   mentioned in his answer to your another question.  

Let us know if it helped.

%macro Date_test(itration=); 
	%do i = 1 %to &itration.;
		PROC SQL; 
		CONNECT TO TERADATA(details) ; 
		CREATE TABLE MAX01 AS SELECT * FROM CONNECTION TO TERADATA 
		( SELECT max(date_col) as d, current_date-1 as curr_date from table; ); 
		QUIT; 　 　 
		
		PROC SQL; 
		SELECT d - curr_date into: diff 
		FROM MAX01; 
		quit; 
		
		data _null_; 
			if &diff.=0 then do; 
				call system('Sample.ksh'); 
			end; 
			else do; 
				x = sleep(3600,1); 
				call execute ('%Date_test();'); 
			end;  
		run;
	%end;
%mend Date_test; 　 
%Date_test(itration=7); /*7 iterations each having sleep of 1 hour*/

[sandeep90]

can you please let me know how it will understand the ksh path

[Kurt_Bremser]

---

@sandeep90 wrote:

can you please let me know how it will understand the ksh path

---

It does not need to. The Korn Shell script will have a "shebang" at the start:

#!/usr/bin/ksh

which tells the system which program should be used to run it.

[Satish_Parida]

You can provide the path here instated of only file name.

call system('/dev/user/sandeep/Sample.ksh');

[sandeep90]

Thank you so much