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
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;
If you give an example of the data you have and what you are trying to achieve it will be very helpful.
Thanks!
begindate is numeric value, formatted in DATE9.
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)
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;
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
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;
Thank you very much! Yes, this code does what I wanted to do. Much appreciated.
Best regards,
Yosh
@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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: