removing the duplicate for selected ones

[Smitha9]

Hi,

I have a dataset example:
A                 B                 from                   To

1001        10.33          10/21/2008          7/28/2008

1002        10.33            7/29/2009          10/2/2010

1003         10.33          10/3/2010          10/24/2011

1004          .                  10/25/2011        12/12/2015

1005        10.33           12/13/2015               .

example output:

A           B              from             to

1001   10.33    10/21/2008      12/13/2016 ( I added extra one year from the last"from")

can I do this in SAS? please let me know.

thank you in advance.

[ballardw]

---

@Smitha9 wrote:

Hi,

I have a dataset example:
A                 B                 from                   To

1001        10.33          10/21/2008          7/28/2008

1002        10.33            7/29/2009          10/2/2010

1003         10.33          10/3/2010          10/24/2011

1004          .                  10/25/2011        12/12/2015

1005        10.33           12/13/2015               .

example output:

A           B              from             to

1001   10.33    10/21/2008      12/13/2016 ( I added extra one year from the last"from")

can I do this in SAS? please let me know.

thank you in advance.

---

You need to describe the rules used to identify "duplicate". Then you need to describe the rules used to indicate which of the "duplicate" values is kept.

None of your records show anything normally considered duplicates. The value of A, From and To differ on each record.

You also show a desired To value that does not appear in your example.

I suspect two things. First that you have some typos in your example data, such as To = 7/28/2008 that I strongly suspect should be 7/28/2009.

Second, you do not want to remove "duplicates" but consolidate consecutive dates. Which brings up the question of are your From and To variables actually SAS date values, as in numeric with a format such as MMDDYY10 assigned. The property of these variables is needed because there are SAS functions that can be used with these values to indicate "consecutive" that are not the case with character values.

And to consolidate we need to know what is the rule for which values of one or more variables indicate the start and end of a group. The shown values for "A" are not very helpful in this case.

We would also need to know rules for selecting values of other variables such as B that may change in the consolidation intervals.

[AMSAS]

Technically the answer to your question "can I do this in SAS?" is YES
As @ballardw states, we need more information to determine what you are attempting to do to provide a more complete answer.

One part we can answer is how to calculate the +1 year, which is covered in the documentation INTNX

Here's a simple example:

data _null_ ;
	/* get todays date */
	date=today() ;
	/* advance it 1 year */
	nextyear=intnx("year",date,1,"same") ;
	/* print the SAS date value to the log */
	put date= nextyear= ;
	/* print formatted SAS date value to the log */
	put date= ddmmyy. nextyear= ddmmyy. ;
run ;

You may also want to review About SAS Date, Time, and Datetime Values.