Dynamic table creation - Macro - Do loop (through a list of elements) works only for first element

makemo — Tue, 24 Mar 2020 17:06:34 GMT

Hello Communities,

I have a macro with a Do loop.

This loops over a list of numbers.
My Do loop executes good only for the first element of the list, and ignores the rest. With no error message.

How should I write my Macro so that the Do Loop performs on every element of the list , not just first one ?

I could not transpose solutions from this post : https://communities.sas.com/t5/SAS-Programming/Macro-loop-stuck-in-the-first-element/td-p/507723 . My case is probably slightly different.

The situation : creating tables with deleted observations

I have the following situation :

- a dataset "table_name" with 2 columns : "Time" and "Quantities"
- I want to create a new table "table_name_N" in which I delete the last N observations of "Quantities"

- To make it easier, I created a 3rd column named "Back" that takes "Time" from the end (see below for utility)

For example, if N=4, we can compare initial data and "table_name_4" :

Initial Data :

Time	Back	Quantities
1	50	100
2	49	100.4
...	...
46	5	108
47	4	108.3
48	3	108.5
49	2	108.9
50	1	109.4

Table to create : "table_name_4"

Time	Back	Quantities
1	50	100
2	49	100.4
...	...
46	5	108
47	4	.
48	3	.
49	2	.
50	1	.

Goal : dynamic creation of tables, whatever the number of tables

I would like a macro that performs this automatically, e.g. :
A) for any value of N
B) for any number of tables I want to create.

That is, if my macro takes as argument the list "4 8 12", I should obtain 3 tables :

- a table "table_name_4" with 4 deleted observations

- a table "table_name_8" with 8 deleted observations

- a table "table_name_12" with 12 deleted observations

My code : what works

I have the code that performs A) and produces the table "table_name_4" :

            data WORK.Table_Name_4.;
            set WORK.Table_Name ;


/* We use the "Back" column to control how many observations we delete */


                        DO i = 1 TO 4 ; 
                              if Back=i then Quantities =. ;
                        END ; 

            run  ;

My code : what does not work
The following code is what should generalize A) so that I can perfom B)

Strangely enough, it does work for the first element of the list, and it does not produce error messages. But other elements of the list are ignored. I get the table "table_name_4", but not the tables for 8 and 12 periods.

Code that does not work : the Macro Definition

%macro Dynamic_Table_Creation (periods = ); 


/* periods is a list of numbers : 4 8 12 or 1 2 3 4, etc...) 
              


/* local macro-variables for the outer loop */ 

%local i next_element ;



/* outer loop : controls how many tables we create */

/* %sysfunc(countw(&periods.)) is the upper bound of the loop, it indicates how many distinct tables are created */

              %do i=1 %to %sysfunc(countw(&periods.));



              /* next_element contains the running number index */


              /* for the value taken by "next_element", we create a table whose suffix is "next_element" 

              %let next_element = %scan(&periods., &i.); 

                            data WORK.table_name_&next_element.; 
                            set WORK.table_name ;


                            /* inner loop */

                            /* We use the "Back" column to control how many observations we delete */

                            %DO i = 1 %TO %eval(&next_element.) ; 
                                          if Back=&i. then Quantities=.;
                            %END ; 
                            run ; 
              %end ; 



%mend ;

Code that does not work : An exemple of macro execution

 %Dynamic_table_creation (periods = 4 8 12) ;

/* then I expect this to create three tables, with suffixes  "_4" , "_8", "_12"

Now if I execute my macro it does good for the first element of my list, and creates "table_name_4". But the rest of the list is ignored.

How should I modify my code so that my loop perform for every element of the list ?

I'm kind of a beginner in Data Management with SAS, and I've being stuck on this single issue for days... Sorry if this question is trivial, but I feel only this forum can help. Any idea appreciated.

I work under SAS Enterprise Guide, and my version of SAS is SAS 9.1.3

Re: Dynamic table creation - Macro - Do loop (through a list of elements) works only for first eleme

ballardw — Tue, 24 Mar 2020 16:43:48 GMT

You may want to consider reformatting your code so that you do not such long lines.

The comment in your Macro definition for Dynamic_Table_Creation that contains the text

 we create a table whose suffix is "next_element"

Is not terminated. So the lines

  %let next_element = %scan(&periods., &i.);
            data WORK.table_name_&next_element.;
            set WORK.table_name ;

are not executed as they are now in the comment. Maybe. As posted this wouldn't have created your first one either

Hint: debug macros by setting OPTIONS MPRINT SYMBOLGEN MLOGIC; before executing. Your log will show the code generated, the assignment of values to macro variables as used and the results of macro logic statements.

You may also have seen something in the log about the same data set being created multiple times.

Re: Dynamic table creation - Macro - Do loop (through a list of elements) works only for first eleme

makemo — Tue, 07 Apr 2020 11:54:48 GMT

Thanks for your input ! It's nice to see people reading carefully and answering questions.

However, the unmatched comments were actually not my problem. It was only some typing mistakes I made when I wrote my post.

Besides, as I was a new contributor, I thought for a moment that my post wasn't posted right... So I posted it twice ! My twin post is here : https://communities.sas.com/t5/SAS-Programming/Dynamic-table-creation-Macro-Do-loop-through-a-list-of-elements/m-p/634698#M188400
And on this post I found the answers I was looking for.

So does anyone know how to delete the current page ? I would like to keep only this question : https://communities.sas.com/t5/SAS-Programming/Dynamic-table-creation-Macro-Do-loop-through-a-list-of-elements/m-p/634698#M188400
Obviously twin questions can lead SAS Communities users into confusions.

Thanks in advance

topic Re: Dynamic table creation - Macro - Do loop (through a list of elements) works only for first eleme in SAS Enterprise Guide

Dynamic table creation - Macro - Do loop (through a list of elements) works only for first element

Re: Dynamic table creation - Macro - Do loop (through a list of elements) works only for first eleme

Re: Dynamic table creation - Macro - Do loop (through a list of elements) works only for first eleme