topic Re: Impute values of some (not all) variables based on where participant ID falls in alphabetical or in SAS Programming

Impute values of some (not all) variables based on where participant ID falls in alphabetical order

lh50 — Fri, 25 Mar 2022 15:05:28 GMT

I have a dataset where participants were sampled from multiple locations that includes several location-specific variables in addition to participant-specific test results. After merging with another results dataset, some participant IDs from the second dataset have no link in the first so they are missing the location information. However, it is obvious which location they correspond to based on their ID value. For example, ABC1002 in the table below comes from the same school/village/region as ABC1001 and 1003.

id	result	result2	village	school_id	school_name	geopoint_lat	geopoint_lng	region
ABC1001	NEGATIVE	2	A	5555	School A	999	999	15
ABC1002
ABC1003	NEGATIVE	2	A	5555	School A	999	999	15

My question: is there an easy way to impute the location-specific variables for participants like this but *not* other variables like 'result' and 'result2' that are specific to each participant? I could obviously do this one at a time, but hoping there is a quicker fix.

Re: Impute values of some (not all) variables based on where participant ID falls in alphabetical or

ballardw — Fri, 25 Mar 2022 15:22:12 GMT

As long as none of the first ID in a sequence are missing the values something like this:

Data have;
  input id $	result $	result2	village $	 ;
datalines;
ABC1001	NEGATIVE	2	A	
ABC1002	. 	 	 	. 	. 	 	 	 
ABC1003	NEGATIVE	2	A	
;

data want;
   set have;
   village=coalescec(village,lag(village));
run;

The Coalescec (for character) and Coalsesce (for numeric) returns the first value that is not missing. The LAG function, when used carefully, returns the value of a variable on the previous record. Actually you can look back quite a number of records but leave that to an actual problem.

Do use the Coalesce function if your variable is numeric. You didn't provide enough information to tell so I create a small example using just a few variables.

Re: Impute values of some (not all) variables based on where participant ID falls in alphabetical or

lh50 — Mon, 28 Mar 2022 14:19:37 GMT

Thanks, this worked to an extent but only for the first instance of a participant with missing values. Where there are multiple in a row (ex, ABC1002 and ABC1003 are both missing in the example I gave) it only imputed values for ABC1002 but not ABC1003. Is there a way to correct this?