How do I merge observations by duplicate ID variable and overwrite missing data with nonmissing data

JackHP — Fri, 08 Nov 2019 21:49:36 GMT

Hello,

I have a dataset that has a few observations with the same value for an ID variable, but, for example, one observation has missing values for Variables Atten1-Atten8 but values from Date1-Date8, and another observation that has the same ID has values for Atten1-Atten8, but missing values for Date1-Date8. How can I merge these observations by ID so that data for missing variables in one observation is overwritten by the observation that has non missing data.

Below is an excerpt of my data. Note there shouldn't be discrepancies between observations with the same ID for variables where there is non missing data, such as for the case of ID 2780, Dose. I'll need to investigate and clean that up, so not worried about addressing that.

Any suggestions are much appreciated, thank you!

ID 0713	Dose 3	Atten1 1	Atten2 .	Atten3 .	Atten4 1	Atten5 .	Atten6 .	Atten7 .	Atten8 .
0713	.	.	.	.	.	.	.	.	.
2780	15	.	1	.	.	.	.	1	1
2780	2	.	.	.	1	.	1	.	.
4176	.	.	.	.	.	.	.	.	.
4176	10	.	.	.	.	.	.	.	1
4540	7	.	.	.	.	.	.	.	.
4540	.	.	.	.	.	.	.	.	.
4886	3	.	.	.	.	.	.	.	.
4886	.	.	.	.	.	.	.	.	.
7911	1	.	1	.	.	.	.	.	.
7911	2	.	.	.	.	.	.	.	.

Re: How do I merge observations by duplicate ID variable and overwrite missing data with nonmissing

Reeza — Fri, 08 Nov 2019 21:53:32 GMT

You should show what you want as output but usually this works for what you want;

data want;
update have(obs=0) have;
by ID;
run;

Re: How do I merge observations by duplicate ID variable and overwrite missing data with nonmissing

JackHP — Mon, 11 Nov 2019 19:19:19 GMT

Thank you, Reeza, for the suggested code and feedback on how to frame questions in a more helpful manner.

The code you shared did exactly what I was looking for - thank you!

topic Re: How do I merge observations by duplicate ID variable and overwrite missing data with nonmissing in SAS Data Management

How do I merge observations by duplicate ID variable and overwrite missing data with nonmissing data

Re: How do I merge observations by duplicate ID variable and overwrite missing data with nonmissing

Re: How do I merge observations by duplicate ID variable and overwrite missing data with nonmissing