Excluding observations that are not part of all datasets

[hwkim286]

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.

[andreas_lds]

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.

[Kurt_Bremser]

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;