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

Schedule a sas program to trigger a shell script

visa — Tue, 27 Feb 2018 10:49:46 GMT

I am new to SAS. The requirement is to trigger a shell script based on the data load. If the data is present for the previous date, then trigger the shell script. Else wait for one hour, check the date again and then on a loop for four times.

The first part of the code is a macro. This macro connects to the database, pulls in two values - Maximum date present and Current date -1. In a ideal scenario, both dates should be same. Like for today(27th Feb), both should be 26th Feb. Then I am storing it in two macro variables.

The second part checks the condition in IF part, if the dates are not equal, it sleeps for an hour, calls the macro again (to check for data load) and then goes to the beginning part of DO loop. In the IF part if it matches, then triggers the shell script, increments the 'i' and gets out of the loop. This is my intention of writing the program.

Following are the issues I am facing:

1. How to return the Max_Dt and Cur_Dt macro variables back to the Data _null_ step every time the macro is triggered? I am facing an error when I try to use the macro variables back in Data -null_ step.

WARNING: Apparent symbolic reference MAX_DT not resolved.

WARNING: Apparent symbolic reference CUR_DT not resolved.

ERROR 386-185: Expecting an arithmetic expression.

ERROR 200-322: The symbol is not recognized and will be ignored.

2. I am not able to trigger the shell the shell script using the Call system command. The log file gets locked, but the shell script never gets invoked. The shell script basically defines triggers a set of sas reports with a defined location for Output and Log files.

3. What are the other loopholes that I might be missing here?

I really appreciate any suggestions to make this code more robust. I have used SAS EG 7.1 and also through Unix to trigger this program.

 

 

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

Re: Schedule a sas program to trigger a shell script

SASJedi — Tue, 27 Feb 2018 13:58:19 GMT

Before attempting to do something like this, you should first ensure that your SAS workspace server is configured to allow execution of shell script commands from SAS. Although is it possible, it's not the default configuration. Without proper configuration of the workspace server, it is not possible to execute shell scripts from SAS using Enterprise Guide. See the article "Using the X and SYSTASK commands from SAS Enterprise Guide" for information.

Next - the execution of your macro is what produces and populates the macro variables max_dt cur_dt. Those variables will not exist yet at the time your DATA step executes (the macro is called by the DATA step, so it hasn't executed yet). Because the DATA step code includes references to those values (if &max_dt.=&cur_dt. then do;), the macro processor attempts to resolve the variables during tokenization of the DATA step for compilation. This is why you are getting these warnings and errors:

WARNING: Apparent symbolic reference MAX_DT not resolved.

WARNING: Apparent symbolic reference CUR_DT not resolved.

ERROR 386-185: Expecting an arithmetic expression.

ERROR 200-322: The symbol is not recognized and will be ignored.

This problem could be circumvented by reading the values from the macro symbol table at execution time with the SYMGET function:

if symget('max_dt')=symget('cur_dt') then do;

Next, macro calls generated by a DATA step using CALL EXECUTE stack code in the input stack while the DATA step is still running, whic will only execute after the DATA step completes execution. Take a look at DOSUBL() as an alternative which allows immediate execution of the generated code.

Re: Schedule a sas program to trigger a shell script

Satish_Parida — Tue, 27 Feb 2018 18:07:00 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 the 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 a shell script

visa — Wed, 28 Feb 2018 08:48:30 GMT

@SASJedi Thank you very much.

I wrote a dummy sas program(like below) to just trigger the Shell script and it worked. That is why I went ahead and used it in the original program.

data _null_;

call system ('abc.ksh');

run;

I used the Symget function and DOSUBL(), I was able to run the script without any errors.

Thank you very much for your detailed explanation.

I am still working on the Call System part to trigger the shell script - The log file gets locked for a long time and the shell never gets invoked when used with the program. I will update about that briefly.

Re: Schedule a sas program to trigger a shell script

SASJedi — Wed, 28 Feb 2018 20:12:18 GMT

SAS system option XWAIT might be the problem. XWAIT waits for the user to enter EXIT on the command line in the shell script window before exiting and returning control to SAS. Try adding EXIT to the end of your shell script...

Mark

Re: Schedule a sas program to trigger a shell script

SASJedi — Wed, 28 Feb 2018 20:58:14 GMT

I really think this would be better handled without using macro variables. I'd just create a PROC SQL view I could test to see if the dates matched, then do it all in a data step. Fewer moving parts and easier to troubleshoot:

PROC SQL ;
   connect to TERADATA(details);
   create VIEW MAX01 AS 
      select max_date format=mmddyy10., curr_date format=mmddyy10.
         from connection to teradata 
         (SELECT max(date_col) as max_date, current_date-1 as curr_date from table; );
   disconnect from teradata;
quit;

/*********************************************************************
 Read the view, always specifying observation #1. This will prevent 
 the DATA step from ever seeing end of file, so it will keep running
 until it sees a STOP statement.
*********************************************************************/
data _null_;
   n=1;
   do i=1 to 6 while ( i < 6);
/*********************************************************************
 Read the view, test to see if the dates match.
*********************************************************************/
      set work.max01 point=n;
/*********************************************************************
 If they match, execute the script
*********************************************************************/
      if max_date = curr_date then
         do;
            call system('c:\temp\sample.bat');
            stop;
         end;
/*********************************************************************
 Otherwise, sleep then try again.
*********************************************************************/
      else
         do;
            x = sleep(3600,1);
            /* This stop just ensures the SAS program doesn't run forever */
            if i=6 then stop; 
         end;
   end;
run;

Re: Schedule a sas program to trigger a shell script

visa — Mon, 05 Mar 2018 08:06:30 GMT

@SASJedi Using a bash command to trigger the ksh shell worked, also with the use of X command. I wrote something along the lines of

call system ( X 'bash sample.ksh');

and it worked! 🙂

I am sure the below solution you have posted would also have worked! Thank you again, you have been a great help!