Loop Code Examples Needed

[ijm_wf]

I have three SAS datasets, ds1, ds2, ds3. Each of the datasets contains one observation and consists of one date variable. I am trying to write some macro code that will keep looping until the values of the date variable in each of the datasets is the same and no dates are missing in either of the datasets. Once that condition is met, the code writes a message to the log via a put statement and ends the loop. Otherwise, the loop waits for 10 seconds and then continues to check for the condition.

Please provide simple examples. Thanks.

[PaigeMiller]

---

@ijm_wf wrote:

I have three SAS datasets, ds1, ds2, ds3. Each of the datasets contains one observation and consists of one date variable. I am trying to write some macro code that will keep looping until the values of the date variable in each of the datasets is the same and no dates are missing in either of the datasets. Once that condition is met, the code writes a message to the log via a put statement and ends the loop. Otherwise, the loop waits for 10 seconds and then continues to check for the condition.

---

Honestly, I don't understand what you want to do with these three data sets. What looping do you want to do?

Please provide examples of these three data sets, and then explain using these example data sets what you want to do with these three data sets and what the final output is, in terms of the actual contents of the data sets.

[ijm_wf]

Let's say I have code that produces the following datasets below. The idea is to keep checking value of the run_date variable in each of the three datasets until the values of run_date in each of the three datasets are all the same and none of the three datasets have a missing run_date like ds3 in the example below.  Once this condition is met, I want to write a message to the log stating "All dates are the same" and then the loop ends, else the loop waits 10 secs and continues to check for the previously mentioned condition. I'm thinking this could be accomplished with a %do %until loop.

Dataset Name: ds1.sas7bdat

Variable: run_date

Observation: 04SEP2019

Dataset Name: ds2.sas7bdat

Variable: run_date

Observation: 04SEP2019

Dataset Name: ds3.sas7bdat

Variable: run_date

Observation: .

[Reeza]

https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Ap...

See Example 11. 

Additionally, consider a data step and/or call execute.

https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

---

@ijm_wf wrote:

I have three SAS datasets, ds1, ds2, ds3. Each of the datasets contains one observation and consists of one date variable. I am trying to write some macro code that will keep looping until the values of the date variable in each of the datasets is the same and no dates are missing in either of the datasets. Once that condition is met, the code writes a message to the log via a put statement and ends the loop. Otherwise, the loop waits for 10 seconds and then continues to check for the condition.

Please provide simple examples. Thanks.

---

[ErikLund_Jensen]

Hi @ijm_wf 

If my understanding is correct, the following macro will work.

It seems that the date in any of the 3 input data sets might be changed by another program during execution of the check, and this raises some issues about locking of the input data sets during execution.

The macro contains test code to simulate a change. It is there so you can play with the macro and study it's behaviour with different input dates, and it should be deleted before real use.

data ds1; date = date(); run;
data ds2; date = date()-1; run;
data ds3; date = date(); run;

%macro test;
	%let maxiterations = 3;
	%let sleeptime = 3;

 	%let sqlobs = 0;
	%let dates =;
	%let itercnt = 0;

	%* Checkloop;
	%do %until ((&sqlobs = 1 and &dates ne %str( )) or &itercnt = &maxiterations);

		%* TEST - code to change an input data set during execution;
		%if &itercnt = 3 %then %do;
			data ds2; date = date(); run;
		%end;
		%* TEST end;

		proc sql noprint;
			select distinct date into :dates separated by ' ' from (
				select date from ds1
				union
				select date from ds2
				union
				select date from ds3
			);
		quit;

		%if not ((&sqlobs = 1 and &dates ne %str( )) or &itercnt = &maxiterations) 
                        %then %let a = %sysfunc(sleep(&sleeptime ,1)); 
                %let itercnt = %eval(&itercnt + 1); 
            %end; 
            %put All dates are equal and not missing - loop ended at &=itercnt; 
    %mend; 
%test; 

[ErikLund_Jensen · Accepted solution]

Hi @ijm_wf 

There was some trouble with the formatting. New code here:

data ds1; date = date(); run;
data ds2; date = date()-1; run;
data ds3; date = date(); run;

%macro test;
	%let maxiterations = 3;
	%let sleeptime = 3;

 	%let sqlobs = 0;
	%let dates =;
	%let itercnt = 0;

	%* Checkloop;
	%do %until ((&sqlobs = 1 and &dates ne %str( )) or &itercnt = &maxiterations);

		%* TEST - code to change an input data set during execution;
		%if &itercnt = 3 %then %do;
			data ds2; date = date(); run;
		%end;
		%* TEST end;

		proc sql noprint;
			select distinct date into :dates separated by ' ' from (
				select date from ds1
				union
				select date from ds2
				union
				select date from ds3
			);
		quit;

		%if not ((&sqlobs = 1 and &dates ne %str( )) or &itercnt = &maxiterations) %then %let a = %sysfunc(sleep(&maxiterations,1));
		%let itercnt = %eval(&itercnt + 1);
	%end;

	%put All dates are equal and not missing - loop ended at &=itercnt;
%mend;
%test;

[hashman]

@ijm_wf:

1. When you're writing something of this nature, you need to account for:

(a) the possibility that while your program cannot open one of the data sets because it's currently locked for an update and make sure that the checking routine itself doesn't get aborted when it happens (which most likely will)

(b) while your program is waiting, it doesn't gobble up CPU time needlessly - which means that an infinite DO cycle is ruled out and you need to use the SLEEP function, making your session hibernate harmlessly for the preset period of seconds

(c) the possibility that the updates finally making all the dates equal and non-missing can take an inordinate time - and so set some reasonable overall waiting limit

2. I don't see any reason to think of the task in terms of a macro when all this can be done much easier and more cleanly in the DATA step. For example:

data file1 file2 file3 ;                                                  
  run_date = 1 ; output file1 ;                                           
  run_date = 1 ; output file2 ;                                           
  run_date = 1 ; output file3 ;                                           
run ;                                                                     
                                                                          
data _null_ ;                                                             
  retain date_var "run_date" seconds_slept 10 wait_limit 30 ;             
                                                                          
  array ds $ 41 ds1-ds3 ("file1" "file2" "file3") ;                       
  array dt      dt1-dt3 ;                                                 
                                                                          
  do N_tries = 1 to divide (wait_limit, seconds_slept) ;                  
    do over ds ;                                                          
      id = open (cats (ds, "(rename=", date_var, "=", vname (dt), ")")) ; 
      if id = 0 then leave ;                                              
      call set (id) ;                                                     
      rc = fetchobs (id, 1) ;                                             
    end ;                                                                 
    if id ne 0 and nmiss (of dt:) = 0 and mean (of dt:) = dt1 then do ;   
      put "All " date_var "values are equal and non-missing." ;           
      stop ;                                                              
    end ;                                                                 
    rc = sleep (seconds_slept) ;                                          
  end ;                                                                   
  put "Wait limit " wait_limit "seconds is exceeded." ;                   
run ;                                                                     

You can add more bells and whistles to it, such as logic to write a message in the log when a data set cannot be open or periodically reporting to it on N_tries, so that the process can be monitored. Note that I set WAIT_TIME to 30 seconds (i.e. limiting N_tries to 3) for the sake of testing, but you'll obviously need to set it for a much bigger, albeit reasonable, number. 

Kind regards

Paul D.