topic Re: Schedule a sas program to trigger shell script in SAS Programming

Schedule a sas program to trigger shell script

visa — Tue, 27 Feb 2018 11:17:58 GMT

%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!

Re: Schedule a sas program to trigger shell script

Kurt_Bremser — Tue, 27 Feb 2018 13:05:13 GMT

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?

Re: Schedule a sas program to trigger shell script

visa — Tue, 27 Feb 2018 13:35:47 GMT

@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;

Re: Schedule a sas program to trigger shell script

Kurt_Bremser — Tue, 27 Feb 2018 13:37:01 GMT

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.

Re: Schedule a sas program to trigger shell script

Satish_Parida — Tue, 27 Feb 2018 18:14:15 GMT

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*/

Re: Schedule a sas program to trigger shell script

sandeep90 — Tue, 27 Mar 2018 12:34:02 GMT

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

Re: Schedule a sas program to trigger shell script

Kurt_Bremser — Tue, 27 Mar 2018 13:14:00 GMT

@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.

Re: Schedule a sas program to trigger shell script

sandeep90 — Wed, 28 Mar 2018 08:06:56 GMT

Thank you so much

Re: Schedule a sas program to trigger shell script

Satish_Parida — Wed, 28 Mar 2018 09:13:39 GMT

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

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