topic Dynamic table creation – Macro – Do loop (through a list of elements) works only for first element in SAS Programming

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

makemo — Wed, 25 Mar 2020 08:57:16 GMT

Hi everyone,

I have a Macro that performs a Do loop over a list of elements (numbers).
This do loop executes good for the first element.

But strangerly, it ignores the rest of the list, and there is no error message displayed.

My question is the following : How do I rewrite my macro so that the Do loop performs for all elements of the list, not just the first one ?

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

The situation : creating a table
with N 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 my 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

New table : "table_name_4"

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

The 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" or any kind of "table_name_N" :

            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 (1) : 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 (2) : the Macro execution

 %Dynamic_table_creation (periods = 4 8 12) ;

/* 4 8 12  can be replaced with any other list of numbers, like 1 2 3 4 5 */

Now if I execute my macro it does good for the first element of my list.
That means "table_name_4" is created.

But strangely, the loop ignores the rest of the list (numbers 8 and 12).

How should I modify my code so that my loop perform for every element of the list ?
That is, so that my loop creates 3 tables ?

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

I'm clearly a beginner for Data Management in SAS, so I'm sorry if this trivial... but I've been stuck on this single issue for days now and can't find what I did wrong.

Thanks in advance, any help would be greatly appreciated.

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

Quentin — Tue, 24 Mar 2020 17:42:52 GMT

Hi,

You have this loop:

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

And inside that loop you have another loop:

%DO i = 1 %TO %eval(&next_element.) ;

You can't use the same variable, i, for both loops. When you do, the inner loop will increment the value of i, so the outer loop only executes once.

You could change the inner loop to :

%DO j = 1 %TO %eval(&next_element.) ;

And make related changes to create a local variable j, and use j in references on the inner loop.

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

makemo — Tue, 24 Mar 2020 17:51:39 GMT

It works !!!

Thank you very much !!!

Quick and good reply, perfect solution to my headaches.

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

yabwon — Tue, 24 Mar 2020 18:06:33 GMT

Hi,

won't something like this be ok?

data table_name;
do time = 1 to 50;
  back = 51-time;
  Quantities = 100 + ranuni(1);
  output;
end;
run;

%macro Dynamic_Table_Creation (periods = ); 
%local i next_element cntw;
%let cntw = %sysfunc(countw(&periods.));
  %do i=1 %to &CNTW.;
    %let next_element = %scan(&periods., &i.); 

    data WORK.table_name_&next_element.; 
    set WORK.table_name ;
     
      if Back <= &next_element. then Quantities=.;
    run ; 
  %end ; 
%mend ;

%Dynamic_Table_Creation(periods=1 2 3 7 13)

All the best

Bart

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

Reeza — Tue, 24 Mar 2020 18:05:35 GMT

Your loop is fine, you forgot to end a comment before the data step is the primary issue.

This is all one comment in your code:

/* 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 */

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

makemo — Wed, 25 Mar 2020 09:02:13 GMT

@Reeza

Yes you're right, I forgot some "*/" . But this is only a mistake I made here when reproducing my code for communities.sas. In my own code the commentaries are fine.

Thank you anyway for the input, I will be more careful next time.

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

makemo — Wed, 25 Mar 2020 11:23:38 GMT

Hi Yabwon,

Yes, I've tried it, and it does the work too !

It seems actually a cleaner and more efficient code, and less prone to errors, because the inner loop is not needed anymore. I will certainly adopt this code, seems just better overall.

Thanks a lot for the input, very well crafted.