topic Re: Iterating Dates in a Do Loop in SAS Programming

Iterating Dates in a Do Loop

StephenOverton — Tue, 27 Oct 2009 18:21:32 GMT

Anybody know how to create a list of dates (date9.) starting at a specified date and ending on the present date? The start date will be given by proc sql and I need every possible date between then and the present date.

To give context, I'm trying to run a query to get a snapshot of information at a point in time starting from the beginning of my company until the present.

From there, I plan on iterating through this list in a macro so I'll be creating the list similar to:

select unit_pk into :Units1 - :Units99999
from PossibleUnits;

Re: Iterating Dates in a Do Loop

CurtisMack — Tue, 27 Oct 2009 18:58:23 GMT

Dates variables are just the number of days since january 1 1960 so all you need to do is a do loop;

data junk;
format date date9.;
do Date = '01JUL1980'd to today();
output;
end;
run;

Re: Iterating Dates in a Do Loop

CurtisMack — Tue, 27 Oct 2009 19:08:51 GMT

On looking at your example a little more. This may be closer to what you are looking for.

%macro test;
%let startdate = %sysfunc(mdy(7,1,1980));
proc sql;
select unit_pk into %do Date = &startdate %to %sysfunc(today());
%if &Date ne &startdate %then %str(,);
:UNIT_%sysfunc(putn(&Date,DATE9.))
%end;
from PossibleUnits;
quit;
%mend;
options mprint;
%test;

Re: Iterating Dates in a Do Loop

StephenOverton — Tue, 27 Oct 2009 20:10:32 GMT

I'm not as skilled as you, this is what I did with your first post:

/* load dates */
data dates;
format date date9.;
do date = '15SEP1999'd to today();
output;
end;
run;
/* load dates into macro variable */
proc sql;
select "'"||put(date,date9.)||"'d" into :dates1 - :dates99999
from dates;
quit;

Re: Iterating Dates in a Do Loop

StephenOverton — Tue, 27 Oct 2009 20:15:03 GMT

My last piece is figured out how to insert this date into the table I'm updating. Anybody know how to do a proc sql insert using a select clause AND some static value?

Ex (where 'dates' in the macro variable):
%macro iterate;
%do x=1 %to &cntdates;
proc sql;
insert into PenetrationHistory
&&dates&x,
select [A BUNCH OF STUFF IS SELECTED HERE] ;
%end;
%mend iterate;

Re: Iterating Dates in a Do Loop

StephenOverton — Tue, 27 Oct 2009 20:18:17 GMT

wait wait, I'm an idiot...its this..

%macro iterate;
%do x=1 %to &cntdates;
proc sql;
insert into PenetrationHistory
select
&&dates&x as audit_date,
[rest of stuff]
;
%end;
%mend iterate;

Just moved the &&dates&x as audit_date inside the select clause. more

Re: Iterating Dates in a Do Loop

StephenOverton — Tue, 27 Oct 2009 20:26:43 GMT

Here is my last problem (I hope):

I stored the dates I'm iterating though as this: '15SEP1999'd

I need to convert these back to strings but I can't get this function working:

%substr(&&dates&x,2,9) as audit_date

In the code:
%macro iterate;
%do x=1 %to &cntdates;
proc sql;
insert into PenetrationHistory
select
%substr(&&dates&x,2,9) as audit_date,
[rest of stuff]
;
%end;
%mend iterate;

Re: Iterating Dates in a Do Loop

StephenOverton — Tue, 27 Oct 2009 20:36:09 GMT

Once again, I figured it out.

Disregard my last question, everything is good now!

Re: Iterating Dates in a Do Loop

kanivan51 — Wed, 31 Jan 2018 05:05:04 GMT

data junk;
 format date date9.;
  do Date = '01JUL1980'd to today() by 'month';
   output;
  end;
run;

May be you know, can we do something like this? For example, i need only end of month dates. How to do that?

Re: Iterating Dates in a Do Loop

lvalencia — Wed, 22 Feb 2023 07:44:33 GMT

to calculate the end of the month use the following:

end_month = intnx("month", today(), -1, 'E');

I'm searching something similar to your question but the increment between the dates for my case should be monthly instead of daily.

Re: Iterating Dates in a Do Loop

ballardw — Wed, 22 Feb 2023 09:51:05 GMT

@lvalencia

Start your own thread and describe what you need.

Adding on to a 13 year old thread is not improving the existing question.

Re: Iterating Dates in a Do Loop

lvalencia — Wed, 22 Feb 2023 10:02:03 GMT

Apologise but thanks @ballardw.
I'm new here and a bit lost.
New post soon.