Hi all,
I am after some assistance.
I have a dataset that I want to fill the white gaps within the data set.
Variables - Date, Index, Month, Year, Name, ID, ID_Name, Value.
Date is just month/year where Month & Year are just functions of the Date Variable.
Index represents a block range of dates that is repeated every time the index is incremented. I require the white space within the blocks of dates to be repeated. The only issue is if there is a change in value within the block range of dates.
Any help would be greatly appreciated.
For example...
Have: | |||||||
Date | Index | Month | Year | Name | ID | ID_Name | Value |
Jan-03 | 1 | 1 | 2003 | . | . | ||
Feb-03 | 1 | 2 | 2003 | . | . | ||
Mar-03 | 1 | 3 | 2003 | . | . | ||
Apr-03 | 1 | 4 | 2003 | John | 45 | Teacher | 101 |
May-03 | 1 | 5 | 2003 | . | . | ||
… | |||||||
Jan-03 | 2 | 1 | 2003 | . | . | ||
Feb-03 | 2 | 2 | 2003 | Bill | 98 | Engineer | 76 |
Mar-03 | 2 | 3 | 2003 | Bill | 98 | Engineer | 80 |
Apr-03 | 2 | 4 | 2003 | . | . | ||
Want: | |||||||
Date | Index | Month | Year | Name | ID | ID_Name | Value |
Jan-03 | 1 | 1 | 2003 | John | 45 | Teacher | 101 |
Feb-03 | 1 | 2 | 2003 | John | 45 | Teacher | 101 |
Mar-03 | 1 | 3 | 2003 | John | 45 | Teacher | 101 |
Apr-03 | 1 | 4 | 2003 | John | 45 | Teacher | 101 |
May-03 | 1 | 5 | 2003 | John | 45 | Teacher | 101 |
… | |||||||
Jan-03 | 2 | 1 | 2003 | Bill | 98 | Engineer | 76 |
Feb-03 | 2 | 2 | 2003 | Bill | 98 | Engineer | 76 |
Mar-03 | 2 | 3 | 2003 | Bill | 98 | Engineer | 80 |
Apr-03 | 2 | 4 | 2003 | Bill | 98 | Engineer | 80 |
All Solved.
Just modified to capture the obs above.
data want;
do until(not missing(value) or last.index);
set have;
by index;
end;
temp=value;
do until(not missing(value) or last.index);
set have;
by index;
_value=temp;output;
end;
drop temp value;
run;
I can fill some of the gaps. But need to fill in the previous too.
data want; update have (obs=0) have; by index; output; run;
All Solved.
Just modified to capture the obs above.
data want;
do until(not missing(value) or last.index);
set have;
by index;
end;
temp=value;
do until(not missing(value) or last.index);
set have;
by index;
_value=temp;output;
end;
drop temp value;
run;
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.