topic Re: Add row in empty table in SAS Programming

Add row in empty table

Toni2 — Sat, 02 Jul 2022 07:56:56 GMT

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;

Re: Add row in empty table

FreelanceReinh — Sat, 02 Jul 2022 08:50:51 GMT

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

Re: Add row in empty table

tarheel13 — Sat, 02 Jul 2022 15:25:20 GMT

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;

Re: Add row in empty table

Toni2 — Sat, 02 Jul 2022 16:04:24 GMT

thanks

Re: Add row in empty table

Toni2 — Sat, 02 Jul 2022 16:04:30 GMT

thanks