Add row in empty table

[Toni2]

hi, i am trying to find the % of missing in a number of character variables in monthly basis

my problem is var2 has no missing values and creates an empty table (miss_a) which is this one with headers:

month

var2

COUNT

PERCENT

I want when miss_a is empty then to add in column PERCENT = 0. However, no action to be taken if miss_a is NOT empty

i would prefer this to be in a separate step rather than in proc freq 

data A;
input month $ var1 $ var2 $;
datalines;
1 2 5
1 . 8
1 . 7
1 5 2
2 7 0
3 3 2
3 2 9
3 . 2
4 7 4
4 . 5
5 6 2
5 2 2
5 . 3
5 2 2
5 . 4
6 8 2
6 2 2
run;

proc freq data=A;
table var2 / noprint missing out=miss_a;
WHERE var2=""; 
by month;
run;

[FreelanceReinh · Accepted solution]

Hi @Toni2,

---

@Toni2 wrote:

hi, i am trying to find the % of missing in a number of character variables in monthly basis

(...)

proc freq data=A;
table var2 / noprint missing out=miss_a;
WHERE var2=""; 
by month;
run;

---

By using that WHERE condition you exclude observations with non-missing VAR2 values, but you need them to compute the percentage.

In PROC SQL the computation is very clear: For each month divide the number of observations with missing VARn by the number of all observations, then multiply by 100.

proc sql;
create table want as
select month, nmiss(var1)/count(*)*100 as misspct_var1,
              nmiss(var2)/count(*)*100 as misspct_var2
from A
group by month;
quit;

Result:

         misspct_    misspct_
month      var1        var2

  1       50.0000        0
  2        0.0000        0
  3       33.3333        0
  4       50.0000        0
  5       40.0000        0
  6        0.0000        0

[Toni2]

thanks

[tarheel13]

you may also try making a Boolean variable first before doing proc freq like this: 

data A2;
    set A;
    var2miss=var2='';
run;

proc freq data=A2;
    tables var2miss*month / missing outpct out=freq1;
run;

[Toni2]

thanks