BookmarkSubscribeRSS Feed
visa
Fluorite | Level 6
 
%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!

8 REPLIES 8
Kurt_Bremser
Super User

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
Fluorite | Level 6

@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
Super User

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
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 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
Calcite | Level 5

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

Kurt_Bremser
Super User

@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
Lapis Lazuli | Level 10

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

 

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

 

sandeep90
Calcite | Level 5
Thank you so much

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
  • 8 replies
  • 1966 views
  • 3 likes
  • 4 in conversation