Hi all,
I need to create start date and end date from a single date column: the date coulumn is as follow; date
03/09/2006
11/02/2008
11/04/2008
12/31/2010
09/13/2012
02/24/2014
From this date, I need to create two columns; start date and end date, no ID variable in this data. Thank you.
proc summary data=have;
var date;
output out=minmax min=startdate max=enddate;
run;
data want;
if _n_=1 then set minmax;
set have;
format startdate enddate mmddyy10. ;
run;
If you prefer an SQL solution:
proc sql;
create table Want as
select *
,min(date) as Date_Min format = mmddyy10.
,max(date) as Date_Max format = mmddyy10.
from Have;
quit;
Thanks for the responses. However, the result that I want is to use every first date as the start date and the the next date minus a month as end date so that the next date will be the next start date. For example
date
03/09/2006
11/02/2008
11/04/2008
12/31/2010
09/13/2012
02/24/2014
the first start date is 03/09/2006 and end date is 10/31/2008
the second start date is 11/02/2008 and end date is 11/03/2008
the third start date is 11/02/2008 and end date is 11/30/2010
and so on.
thanks
So you want N-1 observations?
data want;
set have ;
startdate=lag(date);
if _n_ > 1 ;
enddate=date-1;
format startdate enddate yymmdd10.;
run;
Obs date startdate enddate 1 2008-11-02 2006-03-09 2008-11-01 2 2008-11-04 2008-11-02 2008-11-03 3 2010-12-31 2008-11-04 2010-12-30 4 2012-09-13 2010-12-31 2012-09-12 5 2014-02-24 2012-09-13 2014-02-23
If you want N observations then what do you want the extra one to look like?
Perhaps it has a missing ENDDATE?
data want;
set have end=eof;
startdate=lag(date);
if _n_ > 1 ;
enddate=date-1;
format startdate enddate yymmdd10.;
output;
if eof then do;
startdate=enddate+1;
enddate=.;
output;
end;
run;
Obs date startdate enddate 1 2008-11-02 2006-03-09 2008-11-01 2 2008-11-04 2008-11-02 2008-11-03 3 2010-12-31 2008-11-04 2010-12-30 4 2012-09-13 2010-12-31 2012-09-12 5 2014-02-24 2012-09-13 2014-02-23 6 2014-02-24 2014-02-24 .
Hi,
This solution is brilliant. Thank you.
Hi Tom,
I wonder why the date column is missing the first value (03/09/2006). Is there a way to adjust this? Thank you.
@bioresearch wrote:
Hi Tom,
I wonder why the date column is missing the first value (03/09/2006). Is there a way to adjust this? Thank you.
Drop the DATE column. You no longer need it.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.
Find more tutorials on the SAS Users YouTube channel.