如何统计数据集中每个变量缺失个数

是否有现成的宏程序，统计每个变量缺失个数，并输出到一个数据集变量名与原来的一样

1. %macro convert(varlist);
   
2. %let n=%sysfunc(countw(&varlist));
   
3. %do i=1 %to &n;
   
4. rename=(%scan(&varlist,&i)=%scan(&varlist,&i)2)
   
5. %end;
   
6. %mend convert;
   
7. %macro convert2(varlist);
   
8. %let n=%sysfunc(countw(&varlist));
   
9. %do i=1 %to &n;
   
10.         retain %scan(&varlist,&i);
    
11.         if missing((%scan(&varlist,&i)2)) then %scan(&varlist,&i)+1;
    
12. %end;
    
13. %mend convert;
    
14. /*宏变量var指定要统计的变量列表*/
    
15. %let var = name weight height;
    
16. data tm2(keep=&var);
    
17.         set tm(%convert(&var)) end=last;
    
18.         %convert2(&var)
    
19.         if last then output;
    
20. run;

复制代码

lwien007 发表于 2014-10-20 19:40

能否通过数据字典直接读取数据集内所有变量，然后全部统计，变量很多时不太好用

1. %macro convert(varlist);
   
2. %let n=%sysfunc(countw(&varlist));
   
3. %do i=1 %to &n;
   
4. rename=(%scan(&varlist,&i)=%scan(&varlist,&i)2)
   
5. %end;
   
6. %mend convert;
   
7. %macro convert2(varlist);
   
8. %let n=%sysfunc(countw(&varlist));
   
9. %do i=1 %to &n;
   
10.         retain %scan(&varlist,&i);
    
11.         if missing((%scan(&varlist,&i)2)) then %scan(&varlist,&i)+1;
    
12. %end;
    
13. %mend convert;
    
14. 
    
15. ods listing close;
    
16. ods output Variables=var1(keep=Variable);
    
17. proc contents data=tm;
    
18. run;
    
19. ods output close;
    
20. ods listing;
    
21. 
    
22. proc transpose data=var1 out=var2(keep=col:);
    
23.         var _all_;
    
24. run;
    
25. 
    
26. data _null_;
    
27.         set var2;
    
28.         new = catx(' ',of col:);
    
29.         call symput('var',new);
    
30. run;
    
31. data tm2(keep=&var);
    
32.         set tm(%convert(&var)) end=last;
    
33.         %convert2(&var)
    
34.         if last then output;
    
35. run;

复制代码

小鳄鱼a 发表于 2014-10-23 13:17
能否通过数据字典直接读取数据集内所有变量，然后全部统计，变量很多时不太好用

楼主你好，我也写了一个宏，请试试看：

1. %macro CountMissing(dataset);
   
2.   %if not %sysfunc(exist(&dataset.)) %then %do;
   
3.     %put Dataset &dataset. is not exist, Program will shutdown.;
   
4.   %end;
   
5.   %else %do;
   
6.     proc contents
   
7.       noprint
   
8.       data = &dataset. out = summary;
   
9.     run;
   
10. 
    
11.     proc sort
    
12.       data = summary(keep = VARNUM NAME)
    
13.       out = var_list;
    
14.       by VARNUM;
    
15.     run;
    
16. 
    
17.     data _null_;
    
18.       set var_list end = eof;
    
19.       call symput(strip("VAR") || strip(_N_), strip(NAME));
    
20.       if eof then
    
21.         call symput(strip("PARA_COUNT"), _N_);
    
22.     run;
    
23. 
    
24.     proc sql;
    
25.       create table var_missing_summary as
    
26.       select
    
27.         %do i = 1 %to &PARA_COUNT.;
    
28.           count(*) - count(&&VAR&i.) as &&VAR&i.,
    
29.         %end;
    
30.       count(*) as TOTAL_OBS
    
31.       from &dataset.
    
32.       ;
    
33.     quit;
    
34.   %end;
    
35. %mend;
    
36. 
    
37. data temp;
    
38.   input a b c d;
    
39.   cards;
    
40. 1 2 3 .
    
41. 23 . 112 3245
    
42. 12 323 . 234
    
43. . . 123 234.23
    
44. ;
    
45. run;
    
46. 
    
47. %CountMissing(temp)

复制代码

sniperhgy 发表于 2014-10-23 13:53
楼主你好，我也写了一个宏，请试试看：

无论行不行，先谢谢了，一次回复最多只能给5个论坛币

小鳄鱼a 发表于 2014-10-23 14:41
无论行不行，先谢谢了，一次回复最多只能给5个论坛币

哦，关于行不行，试试就知道了o(∩_∩)o ，也谢谢楼主给的论坛币。

sniperhgy 发表于 2014-10-23 13:53
楼主你好，我也写了一个宏，请试试看：

这个确实很好用啊   呵呵

lwien007 发表于 2014-10-23 13:41

7686
7687   data _null_;
7688           set var2;
7689           new = catx(' ',of col:);
7690           call symput('var',new);
7691   run;

WARNING: In a call to the CATX function, the buffer allocated for the result was not long enough to contain the
         concatenation of all the arguments. The correct result would contain 1702 characters, but the actual result
         may either be truncated to 200 character(s) or be completely blank, depending on the calling environment.
         The following note indicates the left-most argument that caused truncation.
NOTE: 参数“27”（用于函数“CATX”）无效，位置: 行 7689 列 15。
COL1=A001000000 COL2=A001212000 COL3=AdjClpr1 COL4=AvgROE COL5=ClPr COL6=Csrciccd1 COL7=Csrciccd2 COL8=Exchflg
COL9=Fullshr COL10=Hml_mc COL11=Incmope COL12=Lcomnm COL13=Lstflg COL14=Mktflg COL15=OwnCon1 COL16=ROA COL17=Rmrf_mc
COL18=Smb_mc COL19=Stkcd COL20=Trdshr COL21=WkFulTurnR COL22=WkTrdTurnR COL23=Wkaret COL24=Wkret COL25=_FREQ_
COL26=_TYPE_ COL27=be COL28=bm COL29=cash COL30=cashsquare1 COL31=cashsquare2 COL32=cashsquare3 COL33=cashsquare4
COL34=cashsquare5 COL35=cashsquare6 COL36=cashsquare7 COL37=cashsquare8 COL38=cashvot COL39=comfulturnr
COL40=comtrdturnr COL41=cum9 COL42=cv COL43=date COL44=fgs COL45=fullshrvl COL46=gs COL47=gsdiva COL48=h210 COL49=hml
COL50=income COL51=lgasset COL52=lgavgroe1 COL53=lgcash1 COL54=lgcash2 COL55=lgcash3 COL56=lgcash4 COL57=lgcash5
COL58=lgcash6 COL59=lgcash7 COL60=lgcash8 COL61=lgcomfulturnr COL62=lgcomtrdturnr COL63=lggs1 COL64=lginc
COL65=lgincome1 COL66=lgnetppe COL67=lgnstkcd COL68=lgopeca1 COL69=lgopeca2 COL70=lgopeca3 COL71=lgopeca4
COL72=lgopeca5 COL73=lgopeca6 COL74=lgopeca7 COL75=lgopeca8 COL76=lgroa1 COL77=lgstkcd COL78=lgygs1 COL79=lgygs2
COL80=lgygs3 COL81=lgygs4 COL82=lgygs5 COL83=lgygs6 COL84=lgygs7 COL85=lgygs8 COL86=lgyincome1 COL87=lgyincome2
COL88=lgyincome3 COL89=lgyincome4 COL90=lgyincome5 COL91=lgyincome6 COL92=lgyincome7 COL93=lgyincome8 COL94=lgyroa1
COL95=lgyroa2 COL96=lgyroa3 COL97=lgyroa4 COL98=lgyroa5 COL99=lgyroa6 COL100=lgyroa7 COL101=lgyroa8 COL102=lgyroe1
COL103=lgyroe2 COL104=lgyroe3 COL105=lgyroe4 COL106=lgyroe5 COL107=lgyroe6 COL108=lgyroe7 COL109=lgyroe8
COL110=mean_adjclpr1 COL111=meancash COL112=meancashvot COL113=meanopeca COL114=meanopecavot COL115=meanygs
COL116=meanygsvot COL117=meanyincome COL118=meanyincomevot COL119=meanyroa COL120=meanyroe COL121=month
COL122=netppediva COL123=null COL124=opeca COL125=opecasquare1 COL126=opecasquare2 COL127=opecasquare3
COL128=opecasquare4 COL129=opecasquare5 COL130=opecasquare6 COL131=opecasquare7 COL132=opecasquare8 COL133=opecavot
COL134=ri_rf COL135=rm_rf COL136=rsq COL137=smb COL138=std_adjclpr1 COL139=trdshrvl COL140=votfeps COL141=week
COL142=weekday COL143=wkrfret COL144=year COL145=ygs COL146=ygssquare1 COL147=ygssquare2 COL148=ygssquare3
COL149=ygssquare4 COL150=ygssquare5 COL151=ygssquare6 COL152=ygssquare7 COL153=ygssquare8 COL154=ygsvot COL155=yincome
COL156=yincomesquare1 COL157=yincomesquare2 COL158=yincomesquare3 COL159=yincomesquare4 COL160=yincomesquare5
COL161=yincomesquare6 COL162=yincomesquare7 COL163=yincomesquare8 COL164=yincomevot COL165=ym COL166=yroa
COL167=yroasquare1 COL168=yroasquare2 COL169=yroasquare3 COL170=yroasquare4 COL171=yroasquare5 COL172=yroasquare6
COL173=yroasquare7 COL174=yroasquare8 COL175=yroavot COL176=yroe COL177=yroesquare1 COL178=yroesquare2
COL179=yroesquare3 COL180=yroesquare4 COL181=yroesquare5 COL182=yroesquare6 COL183=yroesquare7 COL184=yroesquare8
COL185=yroevot new=  _ERROR_=1 _N_=1
NOTE: 从数据集 WORK.VAR2. 读取了 1 个观测
NOTE: “DATA 语句”所用时间（总处理时间）:
      实际时间          0.01 秒
      CPU 时间          0.01 秒


7692   data tm2(keep=&var);
7693           set kong(%convert(&var)) end=last;
ERROR: %SYSFUNC 或 %QSYSFUNC 宏函数引用的函数 COUNTW 中的参数过少。
ERROR: 在需要数值操作数的 %EVAL 函数或 %IF 条件中发现字符操作数。条件是: &n
ERROR: %TO 值（%DO I 循环中）无效。
ERROR: 宏 CONVERT 将终止执行。
7694           %convert2(&var)
ERROR: %SYSFUNC 或 %QSYSFUNC 宏函数引用的函数 COUNTW 中的参数过少。
ERROR: 在需要数值操作数的 %EVAL 函数或 %IF 条件中发现字符操作数。条件是: &n
ERROR: %TO 值（%DO I 循环中）无效。
ERROR: 宏 CONVERT2 将终止执行。
7695           if last then output;
7696   run;

NOTE: 由于出错，SAS 系统停止处理该步。
WARNING: 数据集 WORK.TM2 可能不完整。该步停止时，共有 0 个观测和 185 个变量。
WARNING: 数据集 WORK.TM2 由于该步已停止，而没有被替换。
NOTE: “DATA 语句”所用时间（总处理时间）:
      实际时间          0.01 秒
      CPU 时间          0.00 秒

引用的时候出现了这个情况

有时侯不使用宏,不用数据集的变量表,也可以实现对确实值的统计. 仅供参考.京剧

1. %let data =sashelp.Electric;
   
2. proc format;
   
3.   value nm    . =1 other =0;
   
4.   value $nm ' ' =1 other =0;
   
5. run;
   
6. proc freq data =&data;
   
7. ods output onewayfreqs =otf(keep =table frequency);
   
8.   tables _all_;
   
9.   format _numeric_ nm. _char_ $nm.;
   
10. run;
    
11. data otf2;
    
12.   if 0=1 then set &data(drop =_all_) nobs =nobsx;
    
13.   set otf;
    
14.   by table notsorted;
    
15.   freq_m =sum(nobsx, -frequency);
    
16.   varname =scan(table,2, ' ');
    
17.   keep varname freq_m;
    
18. run;
    
19. proc transpose data =otf2 out =missingout(drop =_name_);
    
20.   id varname; var freq_m;
    
21. run;

复制代码