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?
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
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.
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;
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
thank you all for the help!!!!
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.