topic Re: Macro code to iterate through list in SAS Programming

Macro code to iterate through list

NKormanik — Fri, 25 Feb 2022 00:15:35 GMT

I accepted an answer a bit too hastily when I tried to ask a question earlier. I wasn't clear enough with my question then. And didn't understand the answers given. So, need to try one more time....

@SASKiwi , @PaigeMiller , @Kurt_Bremser , and helpful community.

I've set up a complete document/program:

"Logistic, All.txt"

At the top of the document is:

%let etf = xyz ;

Then all throughout the document is the following, as appropriate, everywhere "xyz" needs to appear:

&etf.

SAS calls this trick creating and using a macro variable. Pretty straight forward, easy enough, and incredibly useful.

Running the entire long program set in SAS works perfectly. All is good with the entire amalgam of procedure steps. Completion is as desired.

What's next is to act on a whole list, as "xyz" is only ONE of a hundred such.

The complete list, say, is:

xyz

abc

def

lmn

qrs

(etc.)

So, as of this second, to move forward, I would need to replicate the above-mentioned document 100x, manually changing only the "xyz" at the very top of each document, to the appropriate characters, and then running each of the 100 documents through SAS. This can fairly efficiently be done in a single go (not 100 separate runs) -- using the %include approach, in an additionally created document:

"Logistic, All, Complete List, Using 'Include'.txt":

%include "c:\1\Logistic, All -- xyz.txt" ;
%include "c:\1\Logistic, All -- abc.txt" ;
%include "c:\1\Logistic, All -- def.txt" ;
%include "c:\1\Logistic, All -- lmn.txt" ;
%include "c:\1\Logistic, All -- qrs.txt" ;
(etc.)

Instead of going about it in the above manner, I'm pretty sure there is a macro approach. Automatically get each of the list items over into the "xyz" position (at the very top), one at a time, through the entire list, running the entire procedure set at each iteration of each change.

I hope the task is clear enough this time around, and look forward to your responses. Please try to answer as clear as day, as this all seems rather cloudy at present. And hopefully others will see this post and learn something as well. There may be more than one way of accomplishing the task. Please share your thoughts.

Humorously, I don't even know what to call the above challenge, so as to conduct a successful Google search.

Thanks!

Nicholas Kormanik

Re: Macro code to iterate through list

ballardw — Fri, 25 Feb 2022 00:52:06 GMT

One typical way with macros:

%macro dummy (listvar= );

%do i = 1 %to %sysfunc(countw(&listvar.));
   %let onevalue = %scan(&listvar.,&i);
   %put Replace this Put with the code that uses &onevalue. ;
%end;
%mend;


%dummy(listvar= thisword thatword anotherword);

Caveats, if your "word" is going to contain spaces then you need to provide a delimiter (NOT COMMA for simplicity sake) between values and modify the countw and %scan function calls to use that delimiter as third argument.

If you have the values in a data set you can use Proc sql with INTO to place the values into a macro list that will work.

Proc sql noprint;
   select value into : varlist separated by ' '
   from yourdataset
   ;
quit;

Another approach is a data step creating statements and submitting them using the CALL EXECUTE statement. That you can search for on the forum for examples.

Make sure that the generated statements are valid before turning them loose with action, such as my example with %put.

Your %include statements for example look a bit likely to cause all sorts of problems.

Re: Macro code to iterate through list

Tom — Fri, 25 Feb 2022 00:54:31 GMT

The start of your question makes it sound like you have ONE program file. If that is the case you have done 99% of the work of defining a macro. So just do the last couple of steps.

Take your existing file.

Remove the %LET statement.

Add a %MACRO statement at the top. You will need a name for the macro and you will want to define ETF as the parameter to the macro.

Add a %MEND statement at the bottom.

You now have a macro definition.

%macro logistic(etf);
.... rest of the file ....
%mend ;

You might want to save that into its own file. (I recommend using a normal name for the file. One without spaces or punctuation in it and one that ends with .sas. ) So if you named the macro LOGISTIC in the %MACRO statement then call the file logistic.sas.

Now make another program that will loop over your list.

First step is to get the list into the program. Do you have source already for the list? Perhaps a dataset? Or a text file you can read into a dataset?

For something with 100 values I recommend putting it into an actual dataset.

So you could do something as simple as:

data eft_list;
  input eft $80.;
cards;
xyz
abc
def
lmn
qrs
;

Now that you have the data use it to generate one call to the macro for each observation in the dataset. A simple way might be to use CALL EXECUTE.

data _null_;
  set eft_list;
  call execute(catx(' '
   ,'%nrstr(%logistic)('
   ,eft
   ,')'
   ));
run;

You will first need to actually define the macro by running the code that defines it (the part from %MACRO to %MEND). You could either add the body of the macro into that program before the data step that generates the calls.

Or leave it in its own file and use %INCLUDE to define the macro.

So your final run all program might look like:

%include 'logistic.sas';

data eft_list;
  input eft $80.;
cards;
xyz
abc
def
lmn
qrs
;

data _null_;
  set eft_list;
  call execute(catx(' '
   ,'%nrstr(%logistic)('
   ,eft
   ,')'
   ));
run;

Re: Macro code to iterate through list

kiranv_ — Fri, 25 Feb 2022 00:57:32 GMT

one way to do is to create table of your list and then looping through the list. those. Below I am pasting an example, how to do it. create a permanent dataset unlike work dataset i am showing in below example. Creation of the dataset is needed only once, next time onwards you can simply run below macro.

data test;
	myfilename='/home/kiranvenna1/test/fileone.sas';
	output;
	myfilename='/home/kiranvenna1/test/filetwo.sas';
	output;
run;

%macro one;
	proc sql noprint;
		select myfilename into :fname1- from test;
		select count(*) into :cnt from test;
	quit;

	%do i=1 %to &cnt;
		%include "&&fname&i";
	%end;
%mend;

%one

Re: Macro code to iterate through list

Reeza — Fri, 25 Feb 2022 01:08:15 GMT

Step by Step Tutorial on converting a working program to a macro

This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂

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

Examples of common macro usage - includes examples to loop through a list

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

Re: Macro code to iterate through list

NKormanik — Fri, 25 Feb 2022 06:37:38 GMT

I've read that SAS macros do nothing more than create SAS base code.

If this is true, would it be at all helpful to jump straight to base code output to solve the task outlined at top? What would that look like?

I'm finding it really hard to follow exactly what you folks are suggesting. It all looks like a giant jumble. Maybe some sleep will help....

I'll do a Google search on "CALL EXECUTE", as that might create some light.

Seems strange that I've yet to encounter a single example of what I'm attempting to accomplish, though it appears that what I'm pursuing here is basic and reasonable.

Re: Macro code to iterate through list

NKormanik — Fri, 25 Feb 2022 07:04:00 GMT

By the way, what I'm attempting to do reminds me of a 'mail merge.'

1. Given a base document, designate a place for a name.

2. Have list of names.

3. Press the merge button.

Maybe if thought about this way, other solutions within SAS will become apparent.

It's highly likely SAS has such functionality built in.

Re: Macro code to iterate through list

Kurt_Bremser — Tue, 01 Mar 2022 12:30:14 GMT

Remove the %LET from your code, and save it as

logistic_all.sas

(SAS codes should always be saved with that extension, and don't use special characters like blanks or commas)

Next, save your values in a dataset, and set up a master code:

data _null_;
set dataset;
call execute('%nrstr(
  %let etf = ' !! value !! ';
  %include "logistic_all.sas";
)');
run;

After the data step has run, a complete sequence of %LET and %INCLUDE statements will be in the execution queue.

Edit: corrected typo in CALL EXECUTE code (there was a comma where a semicolon is needed).

Re: Macro code to iterate through list

Quentin — Fri, 25 Feb 2022 13:16:15 GMT

Hi,

This a really good question for someone beginning with the macro language. And your analogy to a mail merge is a great analogy for the purpose of a SAS macro. So let's use that analogy.

@NKormanik wrote:

By the way, what I'm attempting to do reminds me of a 'mail merge.'

1. Given a base document, designate a place for a name.

2. Have list of names.

3. Press the merge button.

In a simple mail merge, you create a template (base document) which has static text, and a dynamic field, say NAME. And then to execute the mail merge, you type in a list of 5 names (typically into a table of some sort), and press "RUN", and the mail merge creates 5 documents, each with the value of name_i inserted into the name field.

Here's an example of a SAS macro:

%macro print(name=) ;
  proc print data=sashelp.class ;
    where name="&NAME" ;
  run ;
%mend ;

This macro defines a block of code that is 3 lines long. That code is mostly static text, like the text in the mail merge template. There is one macro variable, referenced by &NAME, which is analogous to the field in a mail merge. Because the macro variable is defined as an input to the macro, it's also referred to as a macro parameter. When you run the above code, SAS creates a macro (i.e. mail merge template, containing some fixed text and one dynamic field).

If you want to use that template, you can call the macro:

options mprint ;
%print(name=Mary)

With that call, SAS will open the template, and execute all the code, and when it sees the field NAME, it will replace that field with the value Mary. And because we turned on the MPRINT option (which is good to always have on), you can see the code that the macro generated (and SAS executed) in the log:

8    %print(name=Mary)
MPRINT(PRINT):   proc print data=sashelp.class ;
MPRINT(PRINT):   where name="Mary" ;
MPRINT(PRINT):   run ;

So a macro really is like a mail merge. If you want to use the template to generate three PROC PRINT steps for three different students, you could call it three times:

%print(name=Mary)
%print(name=James)
%print(name=William)

and that will execute three PROC PRINT steps.

Now sensibly, if you have 100 names, you don't want to have to type in 100 macro calls. As you know, the whole point of a mail merge is that it is data-driven. You have a data table that lists 100 names, and just tell the mail merge to create one document for each name.

There are different options for using data to drive the execution of a macro, many of which have been shown in this thread already. I think for the mail merge analogy, call execute is the easiest to understand. I can store a list of names in a SAS dataset:

data MyNames ;
  input FirstName $10. ;
  cards ;
Mary 
James 
William
;
run ;

That is the data I will feed into my macro (mail-merge template).

Now I can use CALL EXECUTE to execute the macro. The below DATA step reads each record from MyNames. The job of CALL EXECUTE, as used below, is to generate a macro call from each record. And when it creates the macro call, it takes the value from the dataset variable FirstName and places it into the macro call. The code is a little ugly, due to some timing complexity of CALL EXECUTE:

data _null_;
  set MyNames;
  call execute(
    '%nrstr(%%)print(name='||FirstName||')'
   );
run;

The argument to CALL EXECUTE is a text value, which is created by concatenating fixed text with the value of FirstName read from the dataset. When that code runs, the DATA step loops 3 times, which means CALL EXECUTE executes three times, and it generates 3 macro calls, just as if you had typed them in yourself. You can see the macro calls in the log:

NOTE: CALL EXECUTE generated line.
1   + %print(name=Mary      )
2   + %print(name=James     )
3   + %print(name=William   )

Using CALL EXECUTE in this was is an advanced way to use the macro language. Before worrying about CALL EXECUTE, work on understanding a macro. A macro (mail merge template) is a unit of code that includes one or more input parameters (fields). When you run a macro, you manually input a value for each parameter. If you want to use DATA to drive a macro call, one way to do that is to use CALL EXECUTE to run a macro and automatically input a value that is read from a data set.

So one approach for your need to is take the code from your %include program and turn it into a macro with a parameter. Then you can try running the macro manually to get it working, then you can try using CALL EXECUTE to drive the macro calls.

Hope that helps.

--Q.

Re: Macro code to iterate through list

NKormanik — Sat, 26 Feb 2022 00:12:46 GMT

@Quentin @Kurt_Bremser @Reeza @kiranv_ @Tom

Wow, such amazing clarity. I'm finally starting to see the light.

Thank you all so much!

Re: Macro code to iterate through list

NKormanik — Tue, 01 Mar 2022 11:17:26 GMT

@Kurt_Bremser

I've called the ETF variable in the dataset "ETF_List". Maybe that's the problem?

1
2    data _null_;
3    set sas_1.ETF_dataset;
4    call execute('%nrstr(
5    %let etf = ' !! value !! ';
6    %include "c:\0_sas_1\logistic_all.sas",
7    )');
8    run;

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
      5:17
NOTE: Variable value is uninitialized.
NOTE: There were 59 observations read from the data set SAS_1.ETF_DATASET.

NOTE: CALL EXECUTE generated line.
1   + %let etf =            .;%include "c:\0_sas_1\logistic_all.sas",
2   + %let etf =            .;%include "c:\0_sas_1\logistic_all.sas",
ERROR: Open code statement recursion detected.
3   + %let etf =            .;%include "c:\0_sas_1\logistic_all.sas",
ERROR: Incorrect %INCLUDE statement will not be executed. There is a syntax error.
4   + %let etf =            .;%include "c:\0_sas_1\logistic_all.sas",
ERROR: Open code statement recursion detected.
5   + %let etf =            .;%include "c:\0_sas_1\logistic_all.sas",
ERROR: Incorrect %INCLUDE statement will not be executed. There is a syntax error.

etc.

Re: Macro code to iterate through list

Quentin — Tue, 01 Mar 2022 11:47:13 GMT

Hi,

When you are generating code (either with a macro or in this case with CALL EXECUTE), it's always important to review the code that was generated (as shown in the log). In this case, the log shows that the following code was generated:

%let etf =            .;%include "c:\0_sas_1\logistic_all.sas",
%let etf =            .;%include "c:\0_sas_1\logistic_all.sas",

If you highlight that code and submitted it, would it work? No.

You would get a syntax error, because the %include statements ends with a comma instead of a semicolon.

You would have a logical error, because your macro variable ETF is being assigned the value dot (.) not the value ABC.

With that information, you need to then go back and look at your code that generates the bad code:

2    data _null_;
3    set sas_1.ETF_dataset;
4    call execute('%nrstr(
5    %let etf = ' !! value !! ';
6    %include "c:\0_sas_1\logistic_all.sas",
7    )');
8    run;

For the first issue, it looks like there is a typo. You can see on line 6 that there is a comma at the end instead of a semicolon.

For the second issue, look on line 5. You can see that the helpful example code Kurt provided used a generic variable name VALUE, because he didn't know the name of the variable in your real dataset. So yes, if your variable is named ETF_List, you should replace VALUE with ETF_List. The other clue to this problem was the note "NOTE: Variable value is uninitialized." This is really an error message. It means your code referenced a variable that doesn't exist.

As you work on resolving these issues, you want to get to a point where your log from CALL EXECUTE looks like:

1   + %let etf = abc;%include "c:\0_sas_1\logistic_all.sas";
2   + %let etf = def;%include "c:\0_sas_1\logistic_all.sas";

If fact, you might want to try running the above two lines manually, to make sure they work, and give you the desired results. This would confirm that everything in logistic_all.sas is working. After that, you can go back to using CALL EXECUTE to generate the code.

Re: Macro code to iterate through list

Kurt_Bremser — Tue, 01 Mar 2022 12:31:09 GMT

I have corrected the typo in my code. Kudos for finding it.

Re: Macro code to iterate through list

Kurt_Bremser — Tue, 01 Mar 2022 12:32:41 GMT

Since I was talking about storing values in my earlier post, I also named the variable value. So you need to do the same.

Re: Macro code to iterate through list

Tom — Tue, 01 Mar 2022 14:30:18 GMT

SAS is clearly showing you the mistake in your code.

2    data _null_;
3    set sas_1.ETF_dataset;
4    call execute('%nrstr(
5    %let etf = ' !! value !! ';
6    %include "c:\0_sas_1\logistic_all.sas",
7    )');
8    run;

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
      5:17
NOTE: Variable value is uninitialized.

The first one says you are treating VALUE as if it was a character variable, but it is numeric.

The second one says that the variable VALUE has never been given any values. So it did not exist in the source data read from ETF_DATASET.

What is the name of the variable in the ETF_DATASET that you want to use to assign to the macro variable ETF?

Re: Macro code to iterate through list

NKormanik — Wed, 02 Mar 2022 00:09:44 GMT

Goodness. "Press ANY key..."

Where's the "ANY" key??!#

Will retry. Thanks all for the help.

Re: Macro code to iterate through list

Quentin — Wed, 02 Mar 2022 17:51:22 GMT

Writing code that writes code is inherently trickier than writing code. : )