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

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;
1 ACCEPTED SOLUTION

Accepted Solutions
SASJedi
SAS Super FREQ

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.

 

Check out my Jedi SAS Tricks for SAS Users

View solution in original post

6 REPLIES 6
SASJedi
SAS Super FREQ

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.

 

Check out my Jedi SAS Tricks for SAS Users
visa
Fluorite | Level 6

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

SASJedi
SAS Super FREQ

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

 

Check out my Jedi SAS Tricks for SAS Users
visa
Fluorite | Level 6

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

SASJedi
SAS Super FREQ

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;
Check out my Jedi SAS Tricks for SAS Users
Satish_Parida
Lapis Lazuli | Level 10

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

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
  • 6 replies
  • 3344 views
  • 2 likes
  • 3 in conversation