Appending to variable in while loop

[Jim975]

Hi SAS community,

New with SAS, I am looking at appending table names to a string so I can do a call execute on it.

%MacroPullFromDatabase(yyyymm) pulls data from database

Aiming to form a string of "DATA WholeData; SET PulledData_202206 PulledData_202205 ...; RUN;" then run with call execute to form a whole table of data.

I am aware that Y changes every loop and my variable don't seem to append to itself every loop, but I can't figure out how to resolve it.

Appreciate any help from anyone, thanks in advance!

DATA _NULL_;
	n="30JUN2022"D;
	m=INTNX('Month',n,-4, 'E');

	do while(n>=m);
		y= put(n, yymmn6.);
		call execute(cats('%MacroPullFromDatabase(', y, ');'));
		%LET var = CATX(" ", &var., CATS('PulledData_', y));
		n=intnx('month', n, -1,'end');
	end;

	call execute(CATS(&var., ';RUN;'));
RUN;

[WarrenKuhfeld]

%let inside a while only gets executed once, before the code runs. I'm not sure what you are doing, but this approach will not work.

[Quentin]

If you're new to SAS, it's tough to jump into macro language too soon, and call execute adds another layer of complexity.

It's hard to know what you're doing, since you haven't shared much of the code.  To really help you with this, it would be good for you to make a small example showing what you are trying to do, and post the macros, as well as input data, and describe the desired output.

A key to understanding macro language and call execute is to understand timing issues.  As Warren wrote, the %LET statement executes before any data step code executes, and it can't read the value of data step variables (because they don't exist yet).  Also you're using two CALL EXECUTES in one step.  The second call execute will run (and generate code) before the code generated by the first call execute has executed.  So depending on what you're trying to do, this may be another timing challenge.

An alternative to digging deeper into macro language and call execute would be to back up and post a description of your problem.  Often there are non-macro solutions.

[Astounding]

There's some imagination required to figure out the process you are aiming for.  Here is an approach that I think will work.

Don't create a string,  Instead, add each data set as it is created.  For example, the name of the data set coming out of your macro should be simplified to be "Pulled_Data".  Then instead of a %LET statement, use another CALL EXECUTE to append the just-pulled-data.  Replacing the %LET line:

call execute('proc append data=pulled_data base=whole_data; run;');

You still need to calculate y, so that your macro pulls the right data.  But you can re-use the output data set name name if you append it right away, and eliminate the need for a set of names for each month.

[ErikLund_Jensen · Accepted solution]

Hi @Jim975 

You have a library with many data sets pulled monthly from a data source, and you want to build a Data Step that creates a new data set by appending a number of consecutive monthly data sets. Further, you want to use a Data _NULL_ step to the create the Data Step code and execute it immediately by utilizing the CALL EXECUTE function. You want this Data Step:

data WholeData; 
  set 
    PulledData_202206 
    PulledData_202205 
    ...
; 
run;

You are not explicit about which months should be pulled, so I suppose the first and last month to append could be given as yyyymm values in 2 macro variables.

The argument to the CALL EXECUTE function is a string containing SAS code. It can be a little tricky to build the string so it contains syntactically correct code, and it is always a good idea to write the generated code to the log, so it is easy to review the result, and then convert to CALL EXECUTE afterwards, when the code looks as expected. Here is some code that writes the wanted Data Step code:

* Parameters to control operation;
%let MonthFirst = 202111;
%let MonthLast =  202206;

* Data _null_ step to generate code;
data _null_;
  FirstMonthStartDate = input("&MonthFirst"||'01  ',anydtdte.);

  * Initiate generated Data Step code;
  put 'data WholeData;'; 
  put @3 'set'; 

  * Mechanism to loop from &MonthFirst to &MonthLast regardless of shift in year
    and add name of monthly data set to generated code;
  i = 0;
  do while (ThisMonth ne "&MonthLast");
    ThisMonthStartDate = intnx('month',FirstMonthStartDate,i,'B');
    ThisMonth =  put(ThisMonthStartDate,yymmn6.);
    ThisMonthDS = 'PulledData_' || ThisMonth;
    put @5 ThisMonthDS;
    i = i + 1;
    if i > 20 then leave;
  end;

    * Finish generated Data Step;
  put @3 ';'; 
  put 'run;'; 
run;

It writes the following code, which is as expected:

data WholeData;
  set
    PulledData_202111
    PulledData_202112
    PulledData_202201
    PulledData_202202
    PulledData_202203
    PulledData_202204
    PulledData_202205
    PulledData_202206
  ;
run;

Next step is to change the Data Step, so the generated code is executed instead of written to the log. The code in the example above contains five PUT statements, and the third PUT is executed several times in a loop. So the question is: How should the code be modified to use the CALL EXECUTE function instead of PUT?.

Many people think it is necessary to build a string containing the full code, and then use one CALL EXECUTE to send the whole code to execution. But CALL EXECUTE does not run the code, it inserts the argument in the input stream after the step containing the CALL EXECUTE function, and one can use as many CALL EXECUTE statements as necessary to write a whole program to the input stream. So we get the desired result just by changing all the PUT <something> statements to CALL EXECUTE(<something>) instead:

%let MonthFirst = 202111;
%let MonthLast =  202206;

data _null_;
  FirstMonthStartDate = input("&MonthFirst"||'01  ',anydtdte.);

  * Initiate generated Data Step code;
  call execute('data WholeData;'); 
  call execute('set'); 

  * Mechanism to loop from &MonthFirst to &MonthLast regardless of shift in year
    and add name of monthly data set to generated code;
  i = 0;
  do while (ThisMonth ne "&MonthLast");
    ThisMonthStartDate = intnx('month',FirstMonthStartDate,i,'B');
    ThisMonth =  put(ThisMonthStartDate,yymmn6.);
    ThisMonthDS = 'PulledData_' || ThisMonth;
    call execute(ThisMonthDS);
    i = i + 1;
    if i > 20 then leave;
  end;

    * Finish generated Data Step;
  call execute(';'); 
  call execute('run;'); 
run;
 

When I submit the code, I get a log full of errors, because I don't have the monthly data sets, but you can see it works as expected:

NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.04 seconds


NOTE: CALL EXECUTE generated line.
1   + data WholeData;
2   + set
3   + PulledData_202111
4   + PulledData_202112
5   + PulledData_202201
6   + PulledData_202202
7   + PulledData_202203
8   + PulledData_202204
9   + PulledData_202205
10  + PulledData_202206
11  + ;
ERROR: File WORK.PULLEDDATA_202111.DATA does not exist.
ERROR: File WORK.PULLEDDATA_202112.DATA does not exist.
ERROR: File WORK.PULLEDDATA_202201.DATA does not exist.
ERROR: File WORK.PULLEDDATA_202202.DATA does not exist.
ERROR: File WORK.PULLEDDATA_202203.DATA does not exist.
ERROR: File WORK.PULLEDDATA_202204.DATA does not exist.
ERROR: File WORK.PULLEDDATA_202205.DATA does not exist.
ERROR: File WORK.PULLEDDATA_202206.DATA does not exist.
12  + run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.WHOLEDATA may be incomplete.  When this step was stopped there were
         0 observations and 0 variables.
WARNING: Data set WORK.WHOLEDATA was not replaced because this step was stopped.

[Jim975]

Thank you everyone so much for the quick reply. I apologize for the lack of information on the posted issue, I will try to include more description on the code and intent next time. I will be reading up more on macro language, the position of my %LET and call execute.

I find @ErikLund_Jensen's solution closely address what I had in mind for the code. I have learnt a lot by reading through his in-depth explanation of the solution. A sincere thanks for spending so much effort for the reply, it was of great help.

Learning SAS coding isn't easy but I am glad that the SAS community is willing and eager to help, thanks for all the replies.😊