Do while condition on other dataset

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Do while condition on other dataset

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

 

 

 

 


Accepted Solutions
Solution
‎11-27-2015 03:58 PM
Super User
Posts: 17,818

Re: Do while condition on other dataset

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;

View solution in original post


All Replies
Valued Guide
Posts: 858

Re: Do while condition on other dataset

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

Super User
Posts: 17,818

Re: Do while condition on other dataset

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.
Occasional Contributor
Posts: 7

Re: Do while condition on other dataset

Thanks!  

begindate is numeric value, formatted in DATE9. 

Super User
Posts: 17,818

Re: Do while condition on other dataset

[ Edited ]

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)

 

Super User
Super User
Posts: 6,499

Re: Do while condition on other dataset

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;
Occasional Contributor
Posts: 7

Re: Do while condition on other dataset

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

Super User
Posts: 17,818

Re: Do while condition on other dataset

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.
Solution
‎11-27-2015 03:58 PM
Super User
Posts: 17,818

Re: Do while condition on other dataset

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;
Occasional Contributor
Posts: 7

Re: Do while condition on other dataset

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

 

Best regards,

Yosh

Super User
Super User
Posts: 6,499

Re: Do while condition on other dataset


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;
Super User
Posts: 5,081

Re: Do while condition on other dataset

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 462 views
  • 4 likes
  • 5 in conversation