## Incrementing variable backwards

Solved
Occasional Contributor
Posts: 9

# Incrementing variable backwards

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?

Accepted Solutions
Solution
‎01-24-2013 04:11 PM
Posts: 3,167

## Re: Incrementing variable backwards

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

All Replies
Super Contributor
Posts: 543

## Re: Incrementing variable backwards

HI.

This may get you somewhere... or nowhere:

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.

PROC Star
Posts: 8,165

## Re: Incrementing variable backwards

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;

Solution
‎01-24-2013 04:11 PM
Posts: 3,167

## Re: Incrementing variable backwards

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

Occasional Contributor
Posts: 9

## Re: Incrementing variable backwards

thank you all for the help!!!!

🔒 This topic is solved and locked.