SAS基础宏之8：GetMissNum

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

宏解释+源代码

1、宏解释

---

      这个宏用来统计数据集中指定变量的缺失值数量，支持数值变量和字符变量
      最后都汇到一张结果表格中

      之前我用的是把表格转置后，用nmiss和cmiss求横向的数组的缺失值
      这样代码比较简单，但是后来发现proc transpose实在是太慢了，而且耗资源
      这下改了下，直接用sql对列进行计算了，不需要转置了

2、源代码

---

1. %macro GetMissNum(SourceTable,TargetTable,InputVar);
   
2. /**********************************************************************/
   
3. /* 此宏的作用是统计原表中不同变量的缺失值数量。其中SourceTable是原始 */
   
4. /* 表格，SourceTable是结果表格；TargetTable是结果表格；InputVar是原始 */
   
5. /* 表格中的变量，可设多个变量，用空格分隔，也可如下设置：=_Numeric_表 */
   
6. /* 示统计全部数值型变量，=_Character_表示统计全部字符型变量，=_All_表 */
   
7. /* 示统计全部变量。 */
   
8. /* */
   
9. /* 最终结果表格中包含所有指定变量的名称、类型和相应的缺失值数量。 */
   
10. /* */
    
11. /* Created on 2013.5.8 */
    
12. /* Modified on 2013.5.8 */
    
13. /**********************************************************************/
    
14. 
    
15. /* 检查TargetTable的存在性 */
    
16. %if &TargetTable EQ %STR() %then %do;
    
17. %put ERROR: The TargetTable should not be blank, please check it again.;
    
18. %goto exit;
    
19. %end;
    
20. 
    
21. /* 检查InputVar的合法性 */
    
22. %if %UPCASE(&InputVar) NE _NUMERIC_ AND %UPCASE(&InputVar) NE _CHARACTER_ AND %UPCASE(&InputVar) NE _ALL_ %then %do;
    
23. %ChkVar(SourceTable=&SourceTable,InputVar=&InputVar,FlagVarExists=GMN_FlagVarExists);
    
24. 
    
25. %if %SYSFUNC(FIND(&GMN_FlagVarExists,0)) NE 0 %then %do;
    
26. %put ERROR: The InputVar should be _Numeric_, _Character_, _All_ or any variable name in SourceTable, case insensitive and without quotes.;
    
27. %put ERROR: The InputVar "%SCAN(&InputVar,%SYSFUNC(FIND(&GMN_FlagVarExists,0)))" does not exist in SourceTable, please check it again.;
    
28. %goto exit;
    
29. %end;
    
30. %end;
    
31. 
    
32. %if %UPCASE(&InputVar) EQ _NUMERIC_ %then %do;
    
33. proc contents data=&SourceTable position out=GMN_VarList(keep=name type varnum) noprint;
    
34. run;
    
35. 
    
36. /* 检查数值型变量是否存在 */
    
37. %ChkValue(SourceTable=GMN_VarList,
    
38. InputVar=type,
    
39. Value=1,
    
40. FlagValueExists=GMN_FlagNumVarExists);
    
41. 
    
42. %if &GMN_FlagNumVarExists GT 0 %then %do;
    
43. proc sql noprint;
    
44. select NAME into :GMN_NumVarList separated by ' '
    
45. from GMN_VarList
    
46. where TYPE EQ 1;
    
47. quit;
    
48. 
    
49. /* 化简原始表格 */
    
50. data &TargetTable(keep=&GMN_NumVarList drop=GMN_i);
    
51. set &SourceTable;
    
52. array VarList &GMN_NumVarList;
    
53. do GMN_i=1 to dim(VarList);
    
54. if VarList{GMN_i} NE . then VarList{GMN_i}=0;
    
55. else VarList{GMN_i}=1;
    
56. end;
    
57. run;
    
58. 
    
59. %GetStatsForTable(SourceTable=&TargetTable,
    
60. TargetTable=&TargetTable,
    
61. ByFactors=,
    
62. InputVar=&GMN_NumVarList,
    
63. InputVarType=,
    
64. OutputVarType=,
    
65. Weight=,
    
66. Statistic=SUM);
    
67. 
    
68. proc transpose data=&TargetTable out=&TargetTable;
    
69. var &GMN_NumVarList;
    
70. run;
    
71. 
    
72. data &TargetTable(rename=(_NAME_=VarName _LABEL_=VarLabel COL1=MissNum));
    
73. retain _NAME_ _LABEL_ VarType COL1;
    
74. set &TargetTable;
    
75. VarType='N';
    
76. run;
    
77. %end;
    
78. %else %do;
    
79. %put ERROR: There is no numeric variable existed in SourceTable, please check it again.;
    
80. %goto exit;
    
81. %end;
    
82. %end;
    
83. %else %if %UPCASE(&InputVar) EQ _CHARACTER_ %then %do;
    
84. proc contents data=&SourceTable position out=GMN_VarList(keep=name type varnum) noprint;
    
85. run;
    
86. 
    
87. /* 检查字符型变量是否存在 */
    
88. %ChkValue(SourceTable=GMN_VarList,
    
89. InputVar=type,
    
90. Value=2,
    
91. FlagValueExists=GMN_FlagCharVarExists);
    
92. 
    
93. %if &GMN_FlagCharVarExists GT 0 %then %do;
    
94. proc sql noprint;
    
95. select NAME,STRIP(NAME)||'_Temp' into :GMN_CharVarList separated by ' ',:GMN_CharVarList_Temp separated by ' '
    
96. from GMN_VarList
    
97. where TYPE EQ 2;
    
98. quit;
    
99. 
    
100. /* 化简原始表格 */
     
101. data &TargetTable(keep=&GMN_CharVarList_Temp drop=GMN_j);
     
102. set &SourceTable;
     
103. array VarList &GMN_CharVarList;
     
104. array VarList_Temp &GMN_CharVarList_Temp;
     
105. do GMN_j=1 to dim(VarList);
     
106. if VarList{GMN_j} NE "" then VarList_Temp{GMN_j}=0;
     
107. else VarList_Temp{GMN_j}=1;
     
108. end;
     
109. run;
     
110. 
     
111. %GetStatsForTable(SourceTable=&TargetTable,
     
112. TargetTable=&TargetTable,
     
113. ByFactors=,
     
114. InputVar=&GMN_CharVarList_Temp,
     
115. InputVarType=,
     
116. OutputVarType=,
     
117. Weight=,
     
118. Statistic=SUM);
     
119. 
     
120. proc transpose data=&TargetTable out=&TargetTable;
     
121. var &GMN_CharVarList_Temp;
     
122. run;
     
123. 
     
124. data &TargetTable(rename=(_NAME_=VarName _LABEL_=VarLabel COL1=MissNum));
     
125. retain _NAME_ _LABEL_ VarType COL1;
     
126. set &TargetTable;
     
127. VarType='C';
     
128. _NAME_=SUBSTR(_NAME_,1,LENGTH(_NAME_)-5);
     
129. run;
     
130. %end;
     
131. %else %do;
     
132. %put ERROR: There is no character variable existed in SourceTable, please check it again.;
     
133. %goto exit;
     
134. %end;
     
135. %end;
     
136. %else %do;
     
137. proc contents data=&SourceTable position out=GMN_VarList(keep=name type varnum) noprint;
     
138. run;
     
139. 
     
140. /* 筛选指定的变量 */
     
141. %if %UPCASE(&InputVar) NE _ALL_ %then %do;
     
142. %let InputVar_Comma=%PrxChange(InputString=&InputVar,PrxString=s/(\w+)/'$1'/); /* 给InputVar中的代码加引号 */
     
143. %let InputVar_Comma=%SYSFUNC(TRANSLATE(&InputVar_Comma,%STR(,),%STR( ))); /* 替换InputVar中的空格为逗号 */
     
144. 
     
145. proc sql noprint;
     
146. create table GMN_VarList as
     
147. select * from GMN_VarList
     
148. where Name in (&InputVar_Comma)
     
149. order by Name;
     
150. quit;
     
151. %end;
     
152. 
     
153. /* 检查数值型变量是否存在 */
     
154. %ChkValue(SourceTable=GMN_VarList,
     
155. InputVar=type,
     
156. Value=1,
     
157. FlagValueExists=GMN_FlagNumVarExists);
     
158. 
     
159. /* 检查字符型变量是否存在 */
     
160. %ChkValue(SourceTable=GMN_VarList,
     
161. InputVar=type,
     
162. Value=2,
     
163. FlagValueExists=GMN_FlagCharVarExists);
     
164. 
     
165. %if &GMN_FlagNumVarExists GT 0 %then %do;
     
166. proc sql noprint;
     
167. select NAME into :GMN_NumVarList separated by ' '
     
168. from GMN_VarList
     
169. where TYPE EQ 1;
     
170. quit;
     
171. 
     
172. /* 化简原始表格 */
     
173. data GMN_NumMiss(keep=&GMN_NumVarList drop=GMN_k);
     
174. set &SourceTable;
     
175. array VarList &GMN_NumVarList;
     
176. do GMN_k=1 to dim(VarList);
     
177. if VarList{GMN_k} NE . then VarList{GMN_k}=0;
     
178. else VarList{GMN_k}=1;
     
179. end;
     
180. run;
     
181. 
     
182. %GetStatsForTable(SourceTable=GMN_NumMiss,
     
183. TargetTable=GMN_NumMiss,
     
184. ByFactors=,
     
185. InputVar=&GMN_NumVarList,
     
186. InputVarType=,
     
187. OutputVarType=,
     
188. Weight=,
     
189. Statistic=SUM);
     
190. 
     
191. proc transpose data=GMN_NumMiss out=GMN_NumMiss;
     
192. var &GMN_NumVarList;
     
193. run;
     
194. 
     
195. data GMN_NumMiss(rename=(_NAME_=VarName _LABEL_=VarLabel COL1=MissNum));
     
196. retain _NAME_ _LABEL_ VarType COL1;
     
197. set GMN_NumMiss;
     
198. VarType='N';
     
199. run;
     
200. %end;
     
201. %if &GMN_FlagCharVarExists GT 0 %then %do;
     
202. proc sql noprint;
     
203. select NAME,STRIP(NAME)||'_Temp' into :GMN_CharVarList separated by ' ',:GMN_CharVarList_Temp separated by ' '
     
204. from GMN_VarList
     
205. where TYPE EQ 2;
     
206. quit;
     
207. 
     
208. /* 化简原始表格 */
     
209. data GMN_CharMiss(keep=&GMN_CharVarList_Temp drop=GMN_l);
     
210. set &SourceTable;
     
211. array VarList &GMN_CharVarList;
     
212. array VarList_Temp &GMN_CharVarList_Temp;
     
213. do GMN_l=1 to dim(VarList);
     
214. if VarList{GMN_l} NE "" then VarList_Temp{GMN_l}=0;
     
215. else VarList_Temp{GMN_l}=1;
     
216. end;
     
217. run;
     
218. 
     
219. %GetStatsForTable(SourceTable=GMN_CharMiss,
     
220. TargetTable=GMN_CharMiss,
     
221. ByFactors=,
     
222. InputVar=&GMN_CharVarList_Temp,
     
223. InputVarType=,
     
224. OutputVarType=,
     
225. Weight=,
     
226. Statistic=SUM);
     
227. 
     
228. proc transpose data=GMN_CharMiss out=GMN_CharMiss;
     
229. var &GMN_CharVarList_Temp;
     
230. run;
     
231. 
     
232. data GMN_CharMiss(rename=(_NAME_=VarName _LABEL_=VarLabel COL1=MissNum));
     
233. retain _NAME_ _LABEL_ VarType COL1;
     
234. set GMN_CharMiss;
     
235. VarType='C';
     
236. _NAME_=SUBSTR(_NAME_,1,LENGTH(_NAME_)-5);
     
237. run;
     
238. %end;
     
239. 
     
240. data &TargetTable;
     
241. set
     
242. %if &GMN_FlagNumVarExists GT 0 %then %do;
     
243. GMN_NumMiss
     
244. %end;
     
245. %if &GMN_FlagCharVarExists GT 0 %then %do;
     
246. GMN_CharMiss
     
247. %end;
     
248. ;
     
249. run;
     
250. %end;
     
251. 
     
252. /* 删除不必要的表格 */
     
253. proc datasets lib=work nolist;
     
254. delete GMN_:;
     
255. quit;
     
256. 
     
257. %exit:
     
258. %mend;
     
259. 
     
260. 
     
261. %macro Demo();
     
262. 
     
263. %let SourceTable=Cars;
     
264. %let TargetTable=MissNum;
     
265. %let InputVar=Cylinders; /* =_Numeric_表示统计全部数值型变量，=_Character_表示统计全部字符型变量，=_All_表示统计全部变量，大小写不敏感 */
     
266. %GetMissNum(&SourceTable,&TargetTable,&InputVar);
     
267. 
     
268. %mend;

复制代码

扫码加我 拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

分享0 收藏5 回帖

关键词：sas基础 Miss Get Iss MIS 基础 基础

学习了

楼主，请问日期时间型变量用这个宏可以识别出来嘛？

为什么我运行之后总是报这个错误？
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: &GSFT_StatGroup1 EQ 0 AND &GSFT_StatGroup2a EQ 0 AND
       &GSFT_StatGroup2b EQ 0 AND &GSFT_StatGroup3 EQ 0 AND &GSFT_StatGroup4 EQ 0

243、246行貌似有错误