BookmarkSubscribeRSS Feed
StephenOverton
Fluorite | Level 6
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;
11 REPLIES 11
CurtisMack
Fluorite | Level 6
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
Fluorite | Level 6
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
Fluorite | Level 6
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
Fluorite | Level 6
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
Fluorite | Level 6
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
Fluorite | Level 6
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
Fluorite | Level 6
Once again, I figured it out.

Disregard my last question, everything is good now!
kanivan51
Obsidian | Level 7
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
Fluorite | Level 6

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
Super User

@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
Fluorite | Level 6
Apologise but thanks @ballardw.
I'm new here and a bit lost.
New post soon.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 10396 views
  • 3 likes
  • 5 in conversation