topic Re: Counting variables that are missing by group in SAS Programming

Counting variables that are missing by group

AJYass — Thu, 14 Sep 2023 15:03:12 GMT

Hello,

I am not sure how to do what I want. Basically I have a group variable of organizations I am interested in, I would like to look across the data set see how many variables are empty for each organization.

So like data I have:

ORG	Var1	Var2	Var3
OrgA	.	1	0
OrgB	1	1	0
OrgC	.	.	.

Want:

ORG	Count of Missing Var
OrgA	1
OrgB	0
OrgC	3

I have about 40 organizations in the group variable and about 200 variables total.

Thanks in advance!

Re: Counting variables that are missing by group

ballardw — Thu, 14 Sep 2023 15:09:34 GMT

data want;
   set have 
   Countofmissing= cmiss(Var1, var2, var3);
run;

should do it.

If you have lots more variables there may be ways to shorten the syntax with variable lists but we would have to see a more complete description of your data.

Re: Counting variables that are missing by group

AJYass — Thu, 14 Sep 2023 15:22:11 GMT

I do have a lot more variables, I need to look at them all. What info do you think we need?

I tried

proc sort data = Have; by Org; run;

data want;
set Have;
Countofmissing= cmiss(Var1-Var200);
by Org;
run;

But it did not work, the new Countofmissing wasnt counting. They were all equal to 1.

Re: Counting variables that are missing by group

ballardw — Thu, 14 Sep 2023 15:31:33 GMT

@AJYass wrote:

I do have a lot more variables, I need to look at them all. What info do you think we need?

I tried

proc sort data = Have; by Org; run;

data want;
set Have;
Countofmissing= cmiss(Var1-Var200);
by Org;
run;

But it did not work, the new Countofmissing wasnt counting. They were all equal to 1.

You subtracted variable Var200 from Var1.

Use : cmiss( OF var1-var200);

or if all of your variables are actually named like that

cmiss (of var:)

the : is a list builder that says "use all the variables whose names start with VAR (or whatever characters you supply).

Re: Counting variables that are missing by group

AJYass — Thu, 14 Sep 2023 15:58:27 GMT

OH! Okay thanks for point that out.

They are all uniquely named variables unfortunately.

The first is A1_1 and the final is Vstatus so the code should be

data want;
set have;
Countofmissing= cmiss(OF A1_1-Vstatus);
run;

Is that correct? Thanks so much for all the help!

Re: Counting variables that are missing by group

ballardw — Thu, 14 Sep 2023 16:23:25 GMT

If you use a list like "of thisvar -- thatvar" , note there are 2 dashes, then SAS uses the variable positions to build the list and gets all of the variables starting at thisvar and ending at thatvar.

@AJYass wrote:

OH! Okay thanks for point that out.

They are all uniquely named variables unfortunately.

The first is A1_1 and the final is Vstatus so the code should be

data want;
set have;
Countofmissing= cmiss(OF A1_1-Vstatus);
run;

Is that correct? Thanks so much for all the help!