I have 3 variables: ID, days and temperature. For each entry,I need to insert rows of additional days for each ID and I need the temperature to carry over among those rows.
The goal is to have each ID have complete continuous days that have the same temperature. This is what I have:
ID day temp
1001 68 16
1001 73 14
1002 85 17
1002 89 15
1003 91 18
1004 97 19
1004 99 20
1004 106 12
1005 109 15
1006 111 18
1006 115 19
1006 120 20
1006 122 21
And here's what I want:
ID day temp
1001 68 16
1001 69 16
1001 70 16
1001 71 16
1001 72 16
1001 73 14
1001 74 14
1001 75 14
1001 76 14
1001 77 14
1001 78 14
1001 79 14
1001 80 14
1001 81 14
1001 82 14
1001 83 14
1001 84 14
1002 85 17
1002 86 17
1002 87 17
1002 88 17
1002 89 15
1002 90 15
1003 91 18
1003 92 18
1003 93 18
1003 94 18
1003 95 18
1003 96 18
1004 97 19
1004 98 19
1004 99 19
proc sort data=have;
by ID;
run;
data want;
set have;
retain temp ID;
day=day +1;
if first.day then temp= temp+0;
end;
run;
i expect the results to be like this:
ID day temp
1001 68 16
1001 69 16
1001 70 16
1001 71 16
1001 72 16
1001 73 14
1001 74 14
1001 75 14
1001 76 14
1001 77 14
1001 78 14
1001 79 14
1001 80 14
1001 81 14
1001 82 14
1001 83 14
1001 84 14
1002 85 17
1002 86 17
1002 87 17
1002 88 17
1002 89 15
1002 90 15
1003 91 18
1003 92 18
1003 93 18
1003 94 18
1003 95 18
1003 96 18
1004 97 19
1004 98 19
1004 99 19
but somehow i am still getting the old data that I am trying to change. Any help will be greatly appreciated. Thanks
The following code should get you what you want except the last entry which seems inconsistent with your logic:
1004 99 19
It comes out to be:
1004 99 20
based on your logic.
proc sort data=have;
by ID day;
run;
data want;
merge have have (firstobs=2 rename=(id=id2 day=day2 temp=temp2));
output;
if (day2 > day) then do;
do while (day < day2 - 1);
day + 1; output;
end;
end;
keep id day temp;
run;