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:
@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
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.