topic Creating start date and end date from a single date column without ID variable in Statistical Procedures

Creating start date and end date from a single date column without ID variable

bioresearch — Sat, 24 Dec 2022 23:23:41 GMT

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.

Re: Creating start date and end date from a single date column without ID variable

PaigeMiller — Sat, 24 Dec 2022 23:49:18 GMT

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;

Re: Creating start date and end date from a single date column without ID variable

SASKiwi — Sat, 24 Dec 2022 23:53:40 GMT

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;

Re: Creating start date and end date from a single date column without ID variable

bioresearch — Sun, 25 Dec 2022 00:30:29 GMT

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

Re: Creating start date and end date from a single date column without ID variable

Tom — Sun, 25 Dec 2022 04:12:49 GMT

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             .

Re: Creating start date and end date from a single date column without ID variable

bioresearch — Mon, 26 Dec 2022 23:52:52 GMT

Hi,

This solution is brilliant. Thank you.

Re: Creating start date and end date from a single date column without ID variable

bioresearch — Thu, 12 Jan 2023 21:42:58 GMT

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.

Re: Creating start date and end date from a single date column without ID variable

Tom — Thu, 12 Jan 2023 21:55:59 GMT

@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.

Re: Creating start date and end date from a single date column without ID variable

djmangen — Thu, 12 Jan 2023 21:59:54 GMT

Remove the line:
If _n_ > 1;

Both StartDate and EndDate will be missing, but the data line will be there.