topic Re: Keep rows that are mostly complete in SAS Data Management

Keep rows that are mostly complete

amarikow57 — Sat, 06 Mar 2021 17:42:21 GMT

I have a dataset with lots of missing data. Rather than impute, I was informed to build two logistic regression prediction models: (1) include only variables that most participants have observations on and (2) include all variables with participants who have most observations.

Originally I was going to use cmiss(of _ALL_) = 0 to obtain complete cases for the second model, but this left me with an incredibly small sample size. Is there a way to include participants that contain say 90% of the information filled out? That is, if I have 20 variables (columns), then only participants who have information in at least 18 of those columns are kept.

Conversely, is there a way to only keep variables that contain 90% of the information? That is, if I have 50 participants (rows), then variables that contain at least 45 observations will be kept. I suppose I can manually look and drop variable names, but I was hoping for something more efficient.

Thank you in advance!

Re: Keep rows that are mostly complete

yabwon — Sat, 06 Mar 2021 17:51:12 GMT

how about: cmiss(of _ALL_)/20 <= 0.1?

Re: Keep rows that are mostly complete

amarikow57 — Sat, 06 Mar 2021 18:23:42 GMT

Hahaha. The answer was so obvious. Thank you!

Do you know how to do a similar thing for columns instead of rows? Would transposing be the best option?

Re: Keep rows that are mostly complete

yabwon — Sat, 06 Mar 2021 18:28:43 GMT

What do you mean by "similar thing for columns"? Drop column if it has less then 90% of non-missing?

Re: Keep rows that are mostly complete

amarikow57 — Sat, 06 Mar 2021 19:14:16 GMT

Yes.

Re: Keep rows that are mostly complete

PGStats — Sat, 06 Mar 2021 23:06:59 GMT

I have been faced with this problem at some occasions where you have to decide on the combination of variables and observations to retain for an analysis that excludes observations with missing values. I wrote a macro that finds ALL of those combinations, called complete subsets, to help you choose. You might choose, for example, the largest subset (nbVars x nbObs) that includes some key variables.

The macro is described here:

https://communities.sas.com/t5/SAS-Communities-Library/Finding-a-complete-sub-matrix-aka-finding-maximal-bicliques/ta-p/285383

Good luck.

Re: Keep rows that are mostly complete

amarikow57 — Sat, 06 Mar 2021 23:23:29 GMT

If my variable is stored as numeric but to be treated as categorical in analysis, does this macro still work okay with it?

Re: Keep rows that are mostly complete

amarikow57 — Sat, 06 Mar 2021 23:33:38 GMT

Ah. Nevermind. It seems SAS University does not like OPTNET. Thank you though.

Re: Keep rows that are mostly complete

PGStats — Sun, 07 Mar 2021 00:23:43 GMT

Please note that proc optnet was introduced with SAS 9.3TS1M2.