我在Excel中有数据集,其中有可变商店作为空格和&在数据中。我想根据商店以及家庭情况来做某种报告和图表,但是客户希望保持商店名称不变。例如:taba&wv,最高。我如何才能添加MACRO来用这种名称按商店生成表格,以及如何使用宏通过商店生成诸如PROC REPORT,PROC TABULATE和CHARTS之类的报告。请在下面找到示例数据
Shop Year Month Family Value1 Value2 Value3
raoas 2006 january TA12 5 6 0
taba&wv 2008 january TS01 0 1 1
sup top 2008 april TZ05 0 0 1
taba&wv 2006 December TA12 5 6 0
raoas 2008 january TA15 0 2 0
sup top 2008 april TQ05 0 1 1
我要创建的表应如下所示
taba&wv
Year Family sum_value1 Sumvalue2 Sum_value3
sup top
Year Family sum_value1 Sumvalue2 Sum_value3
raoas
Year Family sum_value1 Sumvalue2 Sum_value3
如果您只想为不同商店提供单独的报告(因此不需要使用商店名称来命名数据集或变量),那么只需适当地掩盖商店标题就可以了。例如,这是为每个商店创建PROC TABULATEs:
data have;
infile datalines dsd dlm=',';
input Shop $ Year Month $ Family $ Value1 Value2 Value3 ;
datalines;
raoas,2006,january,TA12,5,6,0
taba&wv,2008,january,TS01,0,1,1
sup top,2008,april,TZ05,0,0,1
taba&wv,2006,December,TA12,5,6,0
raoas,2008,january,TA15,0,2,0
sup top,2008,april,TQ05,0,1,1
;run;
proc sql noprint;
select distinct shop into :shops separated by '|'
from have;
quit;
%macro reports;
%do i=1 %to %sysfunc(countw(%superq(shops),|));
title "PROC TABULATE for %qscan(%superq(shops),&i,|)";
proc tabulate data=have(where=(shop="%qscan(%superq(shops),&i,|)"));
class year month family;
var value:;
table year*month*family
,
(value1 value2 value3)*sum;
run;
%end;
%mend reports;
%reports
%SUPERQ()
不允许宏编译器尝试在解析taba&wv
名称时尝试将其解析为宏变量,%QSCAN()
以及掩码&
和空格。
注意:宏变量作为函数中的参数%SUPERQ()
必须为WITHOUT &
。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句