Hi,
I have a dataset that records only the changes to a variable on the change date. For ex.
ID Date Value
1 19/3/18 2
1 21/3/18 3
1 22/3/18 5
2 19/3/18 1
2 23/3/18 2
The dataset I want is the daily status for the full week. If the Value has not changed, the record should stay the same as the previous day.
ID Date Value
1 19/3/18 2
1 20/3/18 2
1 21/3/18 3
1 22/3/18 5
1 23/3/18 5
1 24/3/18 5
1 25/3/18 5
2 19/3/18 1
2 20/3/18 1
2 21/3/18 1
2 22/3/18 1
2 23/3/18 2
2 24/3/18 2
2 25/3/18 2
I have two datasets currently, one dataset that has the values for all IDs as at 19/3/18 and other dataset that records all the delta entries. I have created another dataset that has a Date variable that goes from 19/3/18 to 25/3/18.
Any help will be greatly appreciated.
Many thanks,
Rohit
If you don't have PROC TIMESERIES, there is compact DATA step solution that use a combination of a SET statement, and "self-MERGE with offset" technique:
data have;
input ID Date :ddmmyy9. Value;
format date date9.;
datalines;
1 19/3/18 2
1 21/3/18 3
1 22/3/18 5
2 19/3/18 1
2 23/3/18 2
run;
data want (drop=nxt_date);
set have (keep=id);
by id;
merge have
have (firstobs=2 keep=date rename=(date=nxt_date));
if last.id then do date=date to '25mar2018'd;
output;
end;
else do date=date to nxt_date-1;
output;
end;
run;
I have put in the specific date value '25mar2018'd as a do loop upper value. But if you need to read in a date, and then find the subsequent SUNDAY, you can put
intnx('week.2',date,0,'E');
in place of '28mar2018'd. The "week.2" interval refers to weeks beginning on Monday. So this just add 0 weeks to DATE, and then aligns the result to the END of the 7-day span.
Do you have SAS ETS module?
I have it in my college lab, so here you go. If you want datastep, let the community know
data have;
input ID $ date :ddmmyy8. Value;
format date date9.;
datalines;
1 19/3/18 2
1 21/3/18 3
1 22/3/18 5
2 19/3/18 1
2 23/3/18 2
;
proc timeseries data=have out=want;
id date interval=day setmissing=previous;
var value;
by id;
run;
Thanks for your reply, but unfortunately I dont have SAS/ETS. Please can you suggest an alternative?
Thanks!
Are the dates always the same between the ID's? If so, this is a great opportunity to use PROC TIMESERIES.
The SETMISSING allows you to specify how to fill in the missing values and there are several methods available. The PREV corresponds to LOCF.
You will need a SAS/ETS license to use this method
data have;
informat ID $1. date ddmmyy10. value 8.;
input ID Date Value;
cards;
1 19/3/18 2
1 21/3/18 3
1 22/3/18 5
2 19/3/18 1
2 23/3/18 2
;
run;
proc timeseries data=have out=want;
by id;
id date interval=day start='19Mar2018'd end='25Mar2018'd setmissing=prev;
var value;
run;
If you don't have PROC TIMESERIES, there is compact DATA step solution that use a combination of a SET statement, and "self-MERGE with offset" technique:
data have;
input ID Date :ddmmyy9. Value;
format date date9.;
datalines;
1 19/3/18 2
1 21/3/18 3
1 22/3/18 5
2 19/3/18 1
2 23/3/18 2
run;
data want (drop=nxt_date);
set have (keep=id);
by id;
merge have
have (firstobs=2 keep=date rename=(date=nxt_date));
if last.id then do date=date to '25mar2018'd;
output;
end;
else do date=date to nxt_date-1;
output;
end;
run;
I have put in the specific date value '25mar2018'd as a do loop upper value. But if you need to read in a date, and then find the subsequent SUNDAY, you can put
intnx('week.2',date,0,'E');
in place of '28mar2018'd. The "week.2" interval refers to weeks beginning on Monday. So this just add 0 weeks to DATE, and then aligns the result to the END of the 7-day span.
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.