Incrementing variable backwards

[elfkitty12]

Hello!  If anyone can help me write the code for the following, I would REALLY appreciate it!

I have a dataset that looks like this:

ID          Fixed_Date     Other variables

Bob          0

Bob          4

Bob          5

Joe          0

Joe          6

Joe          6

Joe          6

Ed            0

Ed            9

Ed            9

Basically, the first observation for each person has a fixed_date of 0.  After that, the fixed_date is the # of days following the first observation.  Problem is, I want only one observation per day.  So in the data above, Joe has 3 observations on day 6 and ed has two observations on day 9.  To fix this problem, I want the last observation (of those that occur on the same day) to stay the same.  Then, I want each previous observation to increment(backwards) one day.  So the new data will look like this:

ID          Fixed_Date          New_date

Bob          0                         0

Bob          3                        3

Bob          5                        5

Joe          0                         0

Joe          6                         4           ***

Joe          6                         5           ***

Joe          6                         6           ***

Ed            0                         0

Ed            9                         8           ***

Ed           9                          9          ***

Can anyone help?

[AncaTilea]

HI.

This may get you somewhere... or nowhere:

*read in your data;

data in;

input ID $ 1-3   Fixed_Date;

cards;

Bob          0

Bob         4

Bob          5

Joe          0

Joe          6

Joe          6

Joe          6

Ed            0

Ed            9

Ed            9

;

run;

*sort by ID, and create a count of how many records for each ID;

*if last or first record within the ID, set the count to zero;

proc sort data = in;by id;

data temp;

    set in;

    by id;

    retain cnt 0;

    if first.id | last.id then cnt = 0;

        else cnt ++ 1;

run;

*sort the data by descending 'cnt';

proc sort data = temp;by id fixed_date descending cnt;run;

data want;

    set temp;

by id;

new_date = fixed_date - cnt;

    if last.id then new_dt = fixed_date;

run;

Please let me know if this is close to what you'd like.

Thanks.

Good luck.

Anca.

[art297]

Why does Bob's second fixdate change from 4 to 3, and why is his new_date equal to 3?

If that was a typo, then I would suggest using a DOW loop in a data step.  E.g.:

data have;

  input ID $   Fixed_Date;

  cards;

Bob          0

Bob         4

Bob          5

Joe          0

Joe          6

Joe          6

Joe          6

Ed            0

Ed            9

Ed            9

;

data want (drop=counter);

  do until (last.Fixed_Date);

    set have;

    by id Fixed_Date notsorted;

    if first.Fixed_Date then counter=0;

    if not (first.Fixed_Date and last.Fixed_Date) then counter+1;

  end;

  do until (last.Fixed_Date);

    set have;

    by id Fixed_Date notsorted;

    counter=counter-1;

    if counter le 0 then New_Date=Fixed_Date;

    else New_Date=Fixed_date-counter;

    output;

  end;

run;

[Haikuo · Accepted solution]

Similar to Art's approach, 2XDOW. If the data came as is, meaning clustered, no sort is needed.

data have;

input ID :$20. Fixed_Date;

cards;

Bob 0

Bob 4

Bob 5

Joe 0

Joe 6

Joe 6

Joe 6

Ed 0

Ed 9

Ed 9

;

data want;

do _i=0 by -1 until (last.fixed_date);

  set have;

  by id fixed_date notsorted;

end;

_last=fixed_date;

do _i=_i by 1 until (last.fixed_date);

  set have;

  by id fixed_date notsorted;

new_date=_last+_i;

output;

end;

drop _:;

run;

proc print;run;

Haikuo

[elfkitty12]

thank you all for the help!!!!