Iterating Dates in a Do Loop

[StephenOverton]

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;

[CurtisMack]

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;

[CurtisMack]

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;

[StephenOverton]

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;

[StephenOverton]

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;

[StephenOverton]

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

[StephenOverton]

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;

[StephenOverton]

Once again, I figured it out.

Disregard my last question, everything is good now!

[kanivan51]

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?

[lvalencia]

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.

[ballardw]

@lvalencia 

Start your own thread and describe what you need.

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

[lvalencia]

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