topic Date substract in SAS Programming

Date substract

Emma8 — Thu, 21 Jan 2021 14:03:49 GMT

I would like to create a "Variable" as below (If there is 3 days difference within the same ID---see ID=1 and Date=2/17/2013 since it was more than 3 days from earlier (2/13/2013), so it would increase by one even one preceding was 2 days (2/15/2013)). Thank you.

ID	Date	Variable
1	8/28/2012	0
1	8/30/2012	0
1	11/5/2012	1
1	1/2/2013	2
1	2/13/2013	3
1	2/13/2013	3
1	2/14/2013	3
1	2/15/2013	3
1	2/15/2013	3
1	2/17/2013	4
1	3/18/2013	5
1	4/15/2013	0
2	5/1/2008	0
2	5/20/2008	1
2	6/14/2008	2

then the same otherwise increase by one:

Re: Date substract

PaigeMiller — Thu, 21 Jan 2021 14:23:59 GMT

@Emma8 wrote:

I would like to create a "Variable" as below (If there is 3 days difference within the same ID---see ID=1 and Date=2/17/2013 since it was more than 3 days from earlier (2/13/2013), so it would increase by one even one preceding was 2 days (2/15/2013)).

Do you mean: If there is greater than a 3 days difference within the same ID

Re: Date substract

Tom — Thu, 21 Jan 2021 14:29:33 GMT

Sounds like you want to count the number of times there is a GAP in the dates with the definition of a GAP being more than 2 days difference. But your example seems off of the first ID.

data have;
  input ID Date :mmddyy. Variable ;
  format date yymmdd10.; 
cards;
1 8/28/2012 0
1 8/30/2012 0
1  11/5/2012 1
1    1/2/2013 2
1    2/13/2013 3
1    2/13/2013 3
1 2/14/2013 3
1 2/15/2013 3
1 2/15/2013 3
1 2/17/2013 4
1    3/18/2013 5
1    4/15/2013 0
2    5/1/2008 0
2   5/20/2008 1
2    6/14/2008 2
;

data want;
  set have ;
  by id ;
  gap = dif(date);
  if first.id then do; gap=0; numgaps=0; end;
  if  gap > 2 then numgaps+1;
run;

proc print;
run;

Obs    ID          Date    Variable    gap    numgaps

  1     1    2012-08-28        0         0       0
  2     1    2012-08-30        0         2       0
  3     1    2012-11-05        1        67       1
  4     1    2013-01-02        2        58       2
  5     1    2013-02-13        3        42       3
  6     1    2013-02-13        3         0       3
  7     1    2013-02-14        3         1       3
  8     1    2013-02-15        3         1       3
  9     1    2013-02-15        3         0       3
 10     1    2013-02-17        4         2       3
 11     1    2013-03-18        5        29       4
 12     1    2013-04-15        0        28       5
 13     2    2008-05-01        0         0       0
 14     2    2008-05-20        1        19       1
 15     2    2008-06-14        2        25       2

Re: Date substract

NicoM — Thu, 21 Jan 2021 15:02:49 GMT

It took me a while to understand what you want, but I think you want to count each time a date is more than two days from an earlier date? If this is the case, then you want to count this in "Variable"? You can achieve this as follows.

data input; 
  attrib 
    ID length=8
    Date length=8 informat=mmddyy10. format=date9.
    Variable length=8
  ;
  infile datalines delimiter='09'x missover dsd; 
  input ID Date Variable;

  datalines;
1	8/28/2012	0
1	8/30/2012	0
1	11/5/2012	1
1	1/2/2013	2
1	2/13/2013	3
1	2/13/2013	3
1	2/14/2013	3
1	2/15/2013	3
1	2/15/2013	3
1	2/17/2013	4
1	3/18/2013	5
1	4/15/2013	0
2	5/1/2008	0
2	5/20/2008	1
2	6/14/2008	2
;
run;

proc sort data=input; 
  by ID Date; 
run; 

data want; 
  set input; 
  by ID; 

  * Retain value from one observation to the other. ;
  retain Variable_Calculated Counted_Date; 

  * Reset if we start with a new ID. ;
  if first.ID then do; 
    Variable_Calculated = 0; 
    Counted_Date = Date; 
  end;
  else if Date - Counted_Date > 2 then do; 
     Variable_Calculated + 1; 
     Counted_Date = Date; 
  end;

  format Counted_Date date9.; 
run; 

proc print data=want;
run;

ID	Date	Variable	Variable_Calculated	Counted_Date
1	28AUG2012	0	0	28AUG2012
1	30AUG2012	0	0	28AUG2012
1	05NOV2012	1	1	05NOV2012
1	02JAN2013	2	2	02JAN2013
1	13FEB2013	3	3	13FEB2013
1	13FEB2013	3	3	13FEB2013
1	14FEB2013	3	3	13FEB2013
1	15FEB2013	3	3	13FEB2013
1	15FEB2013	3	3	13FEB2013
1	17FEB2013	4	4	17FEB2013
1	18MAR2013	5	5	18MAR2013
1	15APR2013	0	6	15APR2013
2	01MAY2008	0	0	01MAY2008
2	20MAY2008	1	1	20MAY2008
2	14JUN2008	2	2	14JUN2008

Based on this, I would say that your value of "Variable" for 15 April 2013 for ID 1 is wrong. Should that not be 6 instead of 0? 15 April is more than 2 days apart from 18 March.

By the way, I am relying on the internal representation of dates in SAS, which is the number of days since 1 January 1960. This means I can just subtract date variables if I am interested in the number of days.