Array

[uabcms]

I have a large excel file for which I need to fill in missing data for a by-group
variable. It has a value of the by-variable for the first observation in
each by-group but the remaining observations are missing the
by-variable . I would like to occupy that field. Also, the file may not have
the same number of observations within each by-group.

[deleted_user]

assuming you have already loaded data from excel, just add this small unit of code[pre]
* after "by" group columns loaded ;
retain _ret_by1 ;
if not missing( &by_var1 ) then _ret_by1 = &by_var1 ;
else &by_var1 = ret_by1 ;
retain _ret_by2 ;
if not missing( &by_var2 ) then _ret_by2 = &by_var2 ;
else &by_var2 = _ret_by2 ;
.... etc ....
drop _ret_: ;
[/pre]
I've streamlined the code so you can extend it for as many by-vars as you need. Just remember to establish the macro vars with the names of the BY-vars to be "retained". The code above should work in SAS9 whatever data-types the BY-vars have.

hope this helps !

PeterC