Do while condition on other dataset

[ynchoir]

Hi, 

I have a question about how to use do while loop, when the condition depends on the value from other datasets.

Specifically, I want to create a semiannual calender dating back from various starting dates to 2002.

Say, data0 contains a list of 100 dates ('begindate'), tagged by a variable 'id'.

What I wrote was:

%macro makecalender;

%Do i = 1 %to 100;

data temp;

set data0;

if id = &i;

run;

data calender&i;

n = 0;

set temp;

do while (begindate - n*183 > '1JAN2002'd);

date = begindate - n*183;

output;

n+1;

end;

run;

%if &i=1 %then %do;

data calender;

set calender&i;

run;

%end;

%if &i>1 %then %do;

data calender;

set calender calender&i;

run;

%end

%mend

which did not work.  The issue seems to lie on the line "do while (begindate - n*183 > '1JAN2002'd);", as when I replaced "begindate-n*183" with some specific inputs (like "27NOV2015"d) then it worked.

Could someone suggest how I might fix this?

Best regards,

Yosh

[Steelers_In_DC]

If you give an example of the data you have and what you are trying to achieve it will be very helpful. 

[Reeza]

Well...what does begindate look like, is it a SAS date, numeric type and formatted with a date format?

Check the proc contents results for the dataset.

[ynchoir]

Thanks!  

begindate is numeric value, formatted in DATE9. 

[Reeza]

You can replace this section with a PROC APPEND that deals with the fact that the table may not exist on the first iteration.

%if &i=1 %then %do;
data calender;
set calender&i;
run;
 
%end;
%if &i>1 %then %do;
data calender;
set calender calender&i;
run;
%end

Becomes

*You may or may not want the force option;
Proc append base=Calendar data=calendar&i FORCE;
Run;

Also, instead of date-n*183 you may want to look into the INTNX function.

INTNX('day', begindate, -n*183)

[Tom]

So you want to take a dataset with 100 observations and blow it up into a dataset with 18,300 observations?

Why would you need to use macor code for that?

Let's make some sample data.

data data0;
   id+1;
   input begindate :date9. @@ ;
   format begindate date9.;
cards;
01JAN1983 06MAR2001
;;;;  

Now let's use a DO loop to generate the file with 183 times as many records and one new variable.

data calendar;
   set data0;
   do date=begindate-183 to begindate;
      output;
   end;
   format date date9.;
run;

[ynchoir]

Thank you.

The thing is I need to go backward from begindate by every 6 months up to 2002.  So I do not know how long I need to loop, so I cannot let n from 1 to 183.  If the begin date is 2003, I want to loop only from 1 to 2, not 183.

Best regards,

Yosh

[Reeza]

Use the INTNX function and your loop end/start can be a variable so you should be able to do this without macro code. Post some sample data and expected output.

[Reeza · Accepted solution]

Here's an example. Use intck to calculate the number of intervals needed for you loop and then a loop. You may want to align your dates within the periods as well, either to the start or end. 

/*Generate sample data*/
data have;
informat date date9.;
format date date9.;
input ID date;
cards;
1 01Jan2015
2 28Feb2015
3 03Jan2011
4 04Feb2005
5 13Apr2004
6 19Jul2007
;
run;

data want;
set have;
*Calculate the number of loops need for interval;
intervals=intck('month6', mdy(1,1, 2002), date);
*Keep start date for comparison/checking;
Date_Start=Date;

do i=1 to intervals;
	*increment date, and create dates aligned to start/end of period for convenience;
	date=intnx('month6', date, -1, 's');
	Date_Aligned_Beginning=intnx('month6', date, 0, 'b');
	Date_Aligned_End=intnx('month6', date, 0, 'e');
	output;
end;

format date_aligned: date9.;
run;

[ynchoir]

Thank you very much!  Yes, this code does what I wanted to do.  Much appreciated.

Best regards,

Yosh

[Tom]

---

@ynchoir wrote:

Thank you.

The thing is I need to go backward from begindate by every 6 months up to 2002.  So I do not know how long I need to loop, so I cannot let n from 1 to 183.  If the begin date is 2003, I want to loop only from 1 to 2, not 183.

Best regards,

Yosh

---

Can you post examples of what you mean by that? Are you saying you want one record for every 6 months?  

Did you mean counting down and stopping if the generated date is before 01JAN2002?  So if the date is in 2015 then you would have about 26 records?

data calendar ;
   set data0;
   format date date9.;
   do nmonth=-6 by -6 until (date < '01JAN2002'd);
      date = intnx('month',begindate,nmonth);
      if date >= '01JAN2002'd then output;
   end;
run;

[Astounding]

It seems like you just want every 183 days, as many as needed.  That doesn't take macro language, and is probably as simple as:

data want;

set have;

do date = begindate to '01jan2002'd by -183;

   output;

end;

run;

Really, that's the whole program ... no macro language, just a simple loop.