BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasstats
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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.

View solution in original post

3 REPLIES 3
Amir
PROC Star

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
Super User Tom
Super User

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
Fluorite | Level 6

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 409 views
  • 0 likes
  • 3 in conversation