DATA Step, Macro, Functions and more

Iterating Dates in a Do Loop

Reply
Contributor
Posts: 50

Iterating Dates in a Do Loop

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

Re: Iterating Dates in a Do Loop

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

Re: Iterating Dates in a Do Loop

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

Re: Iterating Dates in a Do Loop

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

Re: Iterating Dates in a Do Loop

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

Re: Iterating Dates in a Do Loop

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
Contributor
Posts: 50

Re: Iterating Dates in a Do Loop

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

Re: Iterating Dates in a Do Loop

Once again, I figured it out.

Disregard my last question, everything is good now!
Ask a Question
Discussion stats
  • 7 replies
  • 249 views
  • 0 likes
  • 2 in conversation