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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.