英文:
Incrementing inside SAS proc sql
问题
如何逐步定义我的sum函数的参数，而不必写出它们的全部内容？
英文:
I want to create a summary by group (represented by reporting_date) and it has repeating arguments and there should be a better way to do it than shown below:
proc sql;
create table summary_cmr as
select distinct reporting_date, sum(cmr1) as bads_1, sum(cmr2) as bads_2, sum(cmr3) as bads_3, sum(cmr4) as bads_4
from have
group by 1;
quit;
How can I incrementally define the arguments of my sum function, without having to write all of them?
答案1
得分: 3
只使用SAS代码而不是SQL代码。然后，您可以使用变量列表。
proc summary data=have nway;
class reporting_date;
output out=summary_cmr sum(cmr1-cmr4)=bads_1-bads_4 ;
run;
英文:
Just use SAS code instead of SQL code. Then you can use variable lists.
proc summary data=have nway;
class reporting_date;
output out=summary_cmr sum(cmr1-cmr4)=bads_1-bads_4 ;
run;
答案2
得分: 1
%宏 SUMN(N) ;
%本地 I ;
%DO I = 1 %TO &N ;
sum(cmr&I) as bads_&I,
%END ;
sum(0) as _EOL
%MEND ;
proc sql ;
create table summary_cmr (drop=_EOL) as
select reporting_date, %SUMN(10)
from have
group by 1 ;
quit ;
英文:
One way would be to define them using a macro loop, and call the macro in PROC SQL
<pre>
%MACRO SUMN(N) ;
%LOCAL I ;
%DO I = 1 %TO &N ;
sum(cmr&I) as bads_&I,
%END ;
sum(0) as _EOL
%MEND ;
proc sql ;
create table summary_cmr (drop=_EOL) as
select reporting_date, %SUMN(10)
from have
group by 1 ;
quit ;
</pre>
答案3
得分: 1
你可以使用PROC MEANS来自动命名变量：
proc means data=have missing noprint;
class reporting_date;
var cmr1-cmr3;
output out=summary_cmr(where=(_TYPE_ > 0) )
sum= / autoname;
run;
变量将如下所示：
cmr1_Sum cmr2_Sum cmr3_Sum
... ... ...
英文:
You can use PROC MEANS to autoname variables:
proc means data=have missing noprint;
class reporting_date;
var cmr1-cmr3;
output out=summary_cmr(where=(_TYPE_ > 0) )
sum= / autoname;
run;
The variables will look as follows:
cmr1_Sum cmr2_Sum cmr3_Sum
... ... ...
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论