topic Re: dynamic counting of non missing observations in SAS Programming

dynamic counting of non missing observations

Satori — Mon, 13 Feb 2023 20:37:31 GMT

I have a dataset with about 50 variables. I want to do a dynamic counting of non missing values for these variables, meaning that I start with the variable with the most non missing observations (var1), then I want to add a second variable (var2) and count the number of non missing observations for both var1 and var2, whereby the number of non missing is the highest possible among the existing variables.

For example, if I have just five variables:

var1 non missing 1000

var2 non missing 800

var3 non missing 400

var4 non missing 850

var5 non missing 330

I would start with var1, and then count the number of observations with non missing of two variables.

var1+var2 non missing 720

var1+var3 non missing 780

var1+var4 non missing 620

var1+var5 non missing 150

Then var1+var3 give me the most observations, so I would keep them and continue with the most number of observations with non missing for three variables.

var1+var3+var2

var1+var3+var4

var1+var3+var5

etc. until I have the result for all the variables (5 in this example). A possible result being var1+var3+var2+var5+var4

Re: dynamic counting of non missing observations

PaigeMiller — Mon, 13 Feb 2023 20:47:05 GMT

Are we to assume that all these variables are numeric? Or are we to assume that all these variables are character? Or are we to assume that some are numeric and some are character?

There are 50C1 + 50C2 + ... + 50C50 (where the C indicates combinations, 50C2 is the number of ways that 50 variables can be combined 2 at a time) possible combinations, that's 1,125,899,906,842,618, for more than is realistically possible to compute. So now what?

Re: dynamic counting of non missing observations

Quentin — Mon, 13 Feb 2023 20:56:27 GMT

If I'm reading correctly the OP wants 50C1 + 49C1 + 48C1 ... 1C1

That is,

Run PROC MEANS on all 50 variables, select the variable with the highest N. Call that variable X.

Then run PROC MEANS on 49 variables where n(X)=1, and select the variable with the highest N. Call that variable Y.

Then run PROC MEANS on 48 variables where n(X,Y)=2, and select the variable with the highest N Call that variable Z.

Then run PROC MEANS on 47 variables where n(X,Y,Z)=3, and select the variable with the highest N....

So it could be done with 50 PROC MEANS steps in a macro or whatever.

Is that what you want, @Satori ?

I'm curious to the big picture as to why you want to do this. I don't think this sort of stepwise approach will necessarily give you an optimal combination of variables.

Re: dynamic counting of non missing observations

ballardw — Mon, 13 Feb 2023 21:20:27 GMT

@Satori wrote:

I have a dataset with about 50 variables. I want to do a dynamic counting of non missing values for these variables, meaning that I start with the variable with the most non missing observations (var1), then I want to add a second variable (var2) and count the number of non missing observations for both var1 and var2, whereby the number of non missing is the highest possible among the existing variables.

For example, if I have just five variables:

var1 non missing 1000

var2 non missing 800

var3 non missing 400

var4 non missing 850

var5 non missing 330

I would start with var1, and then count the number of observations with non missing of two variables.

var1+var2 non missing 720

var1+var3 non missing 780

var1+var4 non missing 620

var1+var5 non missing 150

Then var1+var3 give me the most observations, so I would keep them and continue with the most number of observations with non missing for three variables.

var1+var3+var2

var1+var3+var4

var1+var3+var5

etc. until I have the result for all the variables (5 in this example). A possible result being var1+var3+var2+var5+var4

Can you show us an example of how you use the Var3 as the second largest instead, for discussion, the 5th variable? I am having a hard time seeing where order of variable actually comes into this if the objective is to determine numbers of nonmissing.

Re: dynamic counting of non missing observations

Satori — Tue, 14 Feb 2023 14:55:16 GMT

yes, that is exactly what I want.

The objective of doing this is to get the highest number of observations with information on all (or most) variables, so I can decide where to make a cutoff on the extra variables. For example if on variable 25 I have 1000 observations and by adding variable 26 (the next one with most non missing) it drops to 100, I will choose to not add variable 26 and the following ones (var26 to var50)

Re: dynamic counting of non missing observations

Satori — Tue, 14 Feb 2023 14:58:03 GMT

I chose Var3 randomly. It could be any of the variables. What I want is to add the variable that keeps the highest number of observations. The order only matters in the sense that I want to keep the highest possible number of obs., and the objective is to see how the number of observations decreases as variables are added.

Re: dynamic counting of non missing observations

PaigeMiller — Tue, 14 Feb 2023 15:45:21 GMT

@Satori wrote:

I chose Var3 randomly. It could be any of the variables. What I want is to add the variable that keeps the highest number of observations. The order only matters in the sense that I want to keep the highest possible number of obs., and the objective is to see how the number of observations decreases as variables are added.

Are we to assume that all these variables are numeric? Or are we to assume that all these variables are character? Or are we to assume that some are numeric and some are character?

Re: dynamic counting of non missing observations

Satori — Tue, 14 Feb 2023 15:50:18 GMT

Variables are numeric and character, but can be converted to be all numeric

Re: dynamic counting of non missing observations

PaigeMiller — Tue, 14 Feb 2023 16:21:17 GMT

I'm going to take a step back now and try to look at the big picture.

Over the years, many statistical methods have been developed to handle missing data. SAS has two PROCs, PROC MI and PROC MIANALYZE, which handles missing values in a statistically appropriate way (depending on certain assumptions) based on imputing values to use in place of the missing values. There is also the EM (expectation maximization) algorithm in PROC PLS (and possibly in other PROCs, I'm not sure) that will handle missing values.

The method you are proposing does not sound like any of this previously developed tools to handle missing values. You might want to consider using one of these methods already in SAS rather than try to invent your own. In fact, I would argue against inventing your own method unless none of the SAS methods already mention give you what you want, which is a way to handle missing values in a specific analysis.

In addition, I think your method might be useful if the missings appear at random; but if they are not random I'm highly suspicious that it will be a good method.

All of this hinges on you explaining what you are going to do once you have your missing analysis completed, and despite several people asking what you plan to do with the results of your missing analysis, you have not yet explained. So before I put more energy into helping you out with this method, I need to understand the next steps, once you have completed this accounting of missing values.

Re: dynamic counting of non missing observations

Satori — Tue, 14 Feb 2023 16:28:42 GMT

actually I already explained the objective: get the highest number of observations with information on all (or most) variables. Doing this dynamic counting will allow me to see the decay in the number of observations by adding an extra variable. The goal is to decide where a cutoff is appropriate. For example if on variable 25 I have 1000 observations and by adding variable 26 (the next one with most non missing for all the previous 25 vars) it drops to 100, I will choose to not add variable 26 (as well as the following vars)

Re: dynamic counting of non missing observations

PaigeMiller — Tue, 14 Feb 2023 16:56:02 GMT

@Satori wrote:
actually I already explained the objective:

No that's not what I was asking. I am (and others are) specifically asking about what happens NEXT, after you complete this missing value accounting. What are you going to do with this result? What is the next analysis?

Re: dynamic counting of non missing observations

Quentin — Tue, 14 Feb 2023 17:12:50 GMT

I'm not sure this method works.

As an example, with this step-wise approach imagine you decide to keep three variables, and select Var1 Var2 Var3.

It's possible that Var1 Var3 Var4 would give you more non-missing records. It all depends on how missing-ness is correlated across the variables.

I think to find an optimal solution, you would have to test all the combinations, as Paige mentioned.

Re: dynamic counting of non missing observations

Tom — Tue, 14 Feb 2023 17:37:50 GMT

@Satori wrote:
actually I already explained the objective: get the highest number of observations with information on all (or most) variables. Doing this dynamic counting will allow me to see the decay in the number of observations by adding an extra variable. The goal is to decide where a cutoff is appropriate. For example if on variable 25 I have 1000 observations and by adding variable 26 (the next one with most non missing for all the previous 25 vars) it drops to 100, I will choose to not add variable 26 (as well as the following vars)

What if you added a numeric variable to your data that was a count of how many missing values it had?

data want;
  set have;
  nmiss=0;
  nmiss = cmiss(of _all_);
run;

Now did a regression to see which variables best predict NMISS.

Would that help?

Re: dynamic counting of non missing observations

Satori — Tue, 14 Feb 2023 19:41:24 GMT

After I get this counting, I will choose which variables to include in my analysis, and I will do entropy balancing for matching treatment and control observations

Re: dynamic counting of non missing observations

Satori — Tue, 14 Feb 2023 20:02:08 GMT

Yes I'm aware of that, so what I would like is the simpler one path version. What I really want is help in writng the macro that does this as I'm not familiar with macros. Basically what I want is to count the number of non missing observations, get the variable with the highest number of non missing observations, then remove all missing observations for this highest variable, then again count the number of non missing, get the variable with highest nonmiss, remove all obs with missing in this var and do this for all 50 vars.

Re: dynamic counting of non missing observations

Quentin — Tue, 14 Feb 2023 20:27:57 GMT

Are you comfortable enough with macros to write a macro that will:

1) Run PROC MEANS on a list of variables

2) Identify the variable with highest number of non-missing variables.

So I'm imagining:

%macro FindVarWithGreatestN(
  data=/*name of dataset*/
 ,list= /*space-delimited list of variables*/
  );

I would start by writing that macro. Then after you have that, you can write an outer macro that would iterate, removing one variable at a time.

If you can't write that macro, can you make some test data, and write the non-macro SAS code you would use to do one iteration? (I'm thinking it's PROC means to get N for each var, then select one variable with the max N (handling ties)).

This doesn't necessarily feel like the right thing to do, but if you want to learn the macro language it could be a useful exercise, so I'd be happy to help.

Re: dynamic counting of non missing observations

PaigeMiller — Tue, 14 Feb 2023 20:35:29 GMT

@Satori wrote:

After I get this counting, I will choose which variables to include in my analysis, and I will do entropy balancing for matching treatment and control observations

Okay, so you will choose variables based upon the pattern of missing values, rather than how good the predictors are? And are these values missing at random, or not missing at random?

Re: dynamic counting of non missing observations

Reeza — Tue, 14 Feb 2023 20:50:59 GMT

Can you provide some sample data to work with? This is doable, probably inadvisable due to statistical methodology but doable. It also doesn't consider other combinations that may reduce your observations the minimum amount, as you need to consider all combos not just in order of highest to lowest.

Re: dynamic counting of non missing observations

Satori — Tue, 14 Feb 2023 20:54:38 GMT

yes. Missing at random.

Re: dynamic counting of non missing observations

Reeza — Tue, 14 Feb 2023 20:58:01 GMT

Look at PROC MI missing pattern report.

https://blogs.sas.com/content/iml/2016/04/18/patterns-of-missing-data-in-sas.html