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;
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
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.