topic Excluding observations that are not part of all datasets in SAS Programming

Excluding observations that are not part of all datasets

hwkim286 — Mon, 05 Aug 2019 06:11:57 GMT

Hello all.

I'm currently working with 6 datasets with ID for each observation and realized that there are observations that are missing in some datasets. For example, ID #7, 24, etc are part of datasets 1, 5, and 6 but not 2, 3, and 4. I would like to write a code that would get rid of these observations so that I would be able to run proc panel without getting the error message "Not enough observations with non-missing values for model statement ... "

Here is the merge code that I have tried so far.

DATA merged;

merge set1 (in = a)

set2 (in = b)

set3 (in = c)

set4 (in = d)

set5 (in = e)

set6 (in = f);

by ID;

if not (a and b and c and d and e and f) then delete;

run;

This has given me error messages written above for observations that are part of all dataset and have no missing values.

I am not very familiar with proc sql so if there is anyone that can give me a solution to this without using proc sql I would greatly appreciate it. I'm working with SAS 9.4.

Re: Excluding observations that are not part of all datasets

andreas_lds — Mon, 05 Aug 2019 06:20:02 GMT

Welcome to the community!

If the log complains about anything, posting the log seems to be a good idea. Also note that code and log are best posted by using the {i}-icon to preserve formatting.

Re: Excluding observations that are not part of all datasets

Kurt_Bremser — Mon, 05 Aug 2019 06:28:26 GMT

A match in the datasets can still contain missing values. You need to get a clear picture of your data first.

If your data step merge completes without ERROR/WARNING/other suspicious NOTEs, but you still have lots of missings, you have to investigate your source data.

PS it's simpler to use a subsetting if:

if a and b and c and d and e and f;