Macro-frq_missing

SAS Macro

计算变量缺失情况

Macro

SAS Macro 计算变量的填充率/缺失个数

在有的是时候，我们需要知道数据集中的每个变量缺失情况及占比。

小编的这个Macro可以实现数据集里的每个变量缺失情况的统计。

%frq_missing(inds=如何需要check的数据集,outds=缺失结果输出的数据集,sasver=SAS语言的版本/CN 或EN);

/*简体中文下运行结果*/

libname final "C:\Users\Administrator\Desktop\ftd";

%frq_missing(inds=final.final,outds=out1,sasver=CN);

/*英文下运行结果*/

libname final "C:\Users\Administrator\Desktop\ftd";

%frq_missing(inds=final.final,outds=out1,sasver=EN);

如下Macro Code

%macro frq_missing(inds,outds,sasver=CN);

%global _missvarlist _nmissvarlist;

options nonotes;

proc format;

value num_f . = "0" low-high = "1" ;

value $char_f " " = "0" other = "1" ;

run;

ods html close;

ods output onewayfreqs=&outds.;

proc freq data=&inds. ;

table _ALL_ /missing ;

format _numeric_ num_f. _character_ $char_f.;

run;

ods output close;

data _Tem_1(rename=(frequency=frequency1 percent=percent1 )) _Tem_2(rename=(frequency=frequency2 percent=percent2 ));

length variable $50;

set &outds.;

%if &sasver.=CN %then %do;

variable = upcase(scan(Table,2,"“"));%end;

%else %do;

variable = upcase(scan(Table,2," "));%end;

value = max(of F_:);

if value = 0 then output _Tem_1;

if value = 1 then output _Tem_2;

keep variable frequency percent ;

run;

proc sort data=_Tem_1 out=_Tem_1 sortseq=linguistic(numeric_collation=on);by variable ;quit;

proc sort data=_Tem_2 out=_Tem_2 sortseq=linguistic(numeric_collation=on);by variable ;quit;

data &outds.;

retain variable Total frequency1 frequency2;

merge _Tem_1 _Tem_2;

by variable;

if missing(frequency1) then frequency1=0 ;

if missing(frequency2) then frequency2=0 ;

Total=frequency1+frequency2;

%if &sasver.=CN %then %do;

label frequency1="缺失个数" frequency2="非空个数" Total="观测个数" variable="变量名称";

%end;

%else %do;

label frequency1="MISSING(N)" frequency2="NOT MISSING(N)" Total="OBS(N)" variable="VARNAME";

%end;

keep variable Total frequency1 frequency2;

run;

proc sql noprint;

select variable into:_missvarlist separated by " " from &outds.(where=(frequency1=Total)) ;

select variable into:_nmissvarlist separated by " " from &outds.(where=(frequency2=Total)) ;

quit;

proc delete data=_Tem_1 _Tem_2;quit;

options notes;

%put NOTE: ALL OBS IS MISSING VAR（MACRO VAR :_missvarlist）：&_missvarlist.;

%put NOTE: ALL OBS IS NOT MISSING VAR (MACRO VAR :_nmissvarlist)：&_nmissvarlist.;

ods html ;

%mend;