topic Incrementing variable backwards in SAS Programming

Incrementing variable backwards

elfkitty12 — Thu, 24 Jan 2013 18:49:28 GMT

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

Ed 0

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?

Re: Incrementing variable backwards

AncaTilea — Thu, 24 Jan 2013 19:45:33 GMT

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

Ed 0

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.

Re: Incrementing variable backwards

art297 — Thu, 24 Jan 2013 20:53:46 GMT

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

Ed 0

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;

Re: Incrementing variable backwards

Haikuo — Thu, 24 Jan 2013 21:11:19 GMT

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

Ed 0

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

Re: Incrementing variable backwards

elfkitty12 — Mon, 28 Jan 2013 04:05:56 GMT

thank you all for the help!!!!