%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!
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?
@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;
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.
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*/
can you please let me know how it will understand the ksh path
@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.
You can provide the path here instated of only file name.
call system('/dev/user/sandeep/Sample.ksh');
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.