How to program complex loops with macro variables?

[sasstats]

Hello together,

for a specific event day (day_1st) I need to check if there was time in years before and after this event day. For some years (2012 -2016)  I have the begin and end of e.g. membership in this year. So, I can look backward and forward from my specific date to count the years of membership before and after this date.

My data test looks like this

obs

day_1st

year_1st

begin_2012

end_2012

begin_2013

end_2013

begin_2014

end_2014

begin_2015

end_2015

begin_2016

end_2016

1

15/08/2015

2015

01/01/2012

31/12/2012

01/01/2013

31/12/2013

01/01/2014

31/12/2014

01/01/2015

31/12/2015

01/01/2016

31/12/2016

2

15/11/2015

2015

01/01/2012

31/12/2012

01/01/2013

31/12/2013

01/01/2014

31/12/2014

01/01/2015

31/12/2015

01/01/2016

31/12/2016

3

15/11/2016

2016

.

.

.

.

.

.

01/05/2015

31/12/2015

01/01/2016

31/12/2016

4

15/11/2013

2013

01/01/2012

31/12/2012

01/01/2013

31/12/2013

01/01/2014

31/12/2014

01/01/2015

31/12/2015

01/01/2016

31/12/2016

5

15/05/2016

2016

.

.

.

.

01/08/2014

31/12/2014

01/01/2015

31/12/2015

01/01/2016

31/12/2016

My problem is that a program without loops is very long and it gets more complicated with more years of membership and more years to look backward and forward.

My program without loops works and it is attached (without_loops.sas)

The result of this program looks like this:

obs

day_1st

year_1st

begin_2012

end_2012

begin_2013

end_2013

begin_2014

end_2014

begin_2015

end_2015

begin_2016

end_2016

years_before

years_after

1

15/08/2015

2015

01/01/2012

31/12/2012

01/01/2013

31/12/2013

01/01/2014

31/12/2014

01/01/2015

31/12/2015

01/01/2016

31/12/2016

3

1

2

15/11/2015

2015

01/01/2012

31/12/2012

01/01/2013

31/12/2013

01/01/2014

31/12/2014

01/01/2015

31/12/2015

01/01/2016

31/12/2016

3

1

3

15/11/2016

2016

.

.

.

.

.

.

01/05/2015

31/12/2015

01/01/2016

31/12/2016

1

0

4

15/11/2013

2013

01/01/2012

31/12/2012

01/01/2013

31/12/2013

01/01/2014

31/12/2014

01/01/2015

31/12/2015

01/01/2016

31/12/2016

1

3

5

15/05/2016

2016

.

.

.

.

01/08/2014

31/12/2014

01/01/2015

31/12/2015

01/01/2016

31/12/2016

1

0

I would like to shorten this long program by using loops. But the loops don’t work.

Do you have a solution for my problem?

%macro test;

data test2;

set test;

      %do jahr_num=2011 %to 2016;

            %if jahr_1st=jahr_num %then %do;   /* 1. and main problem: the comparison is always false */

                  %do i_num=1 %to 3;

                  %let jahr_m1=%eval(&jahr-&i.); /* 2. %eval gives a mistake */

            if begin_&jahr_m1. <= intnx("year", tag_1st,-&i., "same") and /* 3. problem intnx doesn't accept a macro

variable */

                        end_&jahr_m1.="31Dec&jahr_m1."d and begin_&jahr.<=tag_1st

                        then years_before=&i.;

                  %end;

            %end;

      %end;

run;

%mend test;

%test;

Thank you very much

sasstats

[Amir]

Hi,

(1) In the line:

%if jahr_1st=jahr_num %then %do;   /* 1. and main problem: the comparison is always false */

if jahr_1st and jahr_num are macro variables then they should have an ampersand in front of them, e.g:

%if &jahr_1st=&jahr_num %then %do;   /* 1. and main problem: the comparison is always false */

(2) In the line:

%let jahr_m1=%eval(&jahr-&i.); /* 2. %eval gives a mistake */

should "&jahr" be "&jahr_num", as created by the %do loop control?

(3) For line:

if begin_&jahr_m1. <= intnx("year", tag_1st,-&i., "same") and /* 3. problem intnx doesn't accept a macro

variable */

please show the error message from the log with surrounding log lines.

(4) In line:

end_&jahr_m1.="31Dec&jahr_m1."d and begin_&jahr.<=tag_1st

should "begin_&jahr" be "begin_&jahr_m1"?

Regards,

Amir.

[Tom · Accepted solution]

Doesn't look like a macro problem at all. Just use arrays.

array begin (2012:2016) begin_2012-begin_2016 ;
array end (2012:2016) end_2012-end_2016 ;

And write your logic using array indexes instead. Perhaps something like this.

years_before=0;
do i=year_1st to 2012 by -1 while (not missing(begin(i))); 
  years_before=years_before+1;
end;

Then you could use macro variables to make the upper (2016) and lower (2012) limits be flexible without changing the logic of the SAS program.

[sasstats]

Thank you very much!

I didn't know this trick with the array enummeration 2012:2016!

This will help me in outher cases too.

With these arrays I was able to sovle my programming problem.

Thank you!