SAS:合并执行命令 [英] SAS: merge in a do command

问题描述

假设我有以下两个单行数据集:

Say that I have the two following one row datasets:

data have_1;
input message $ order_num time price qty;
datalines;
A 3 34199 10 500 
run;

data have_2;
input message $ order_num time delete_qty ;
datalines;
B 2 34200 100 
run;

我有另一个数据集，它聚合了以前的 order_numbers.

I have another dataset that aggregates previous order_numbers.

data total;
input order_num time price qty;
datalines;
1 34197 11 550
2 34198 10.5 450
run;

我的目标是我需要在循环中使用数据集 have_1 和 have_2 更新数据集 total.当我从 have_1 开始时，message=A 意味着我必须通过简单地向 添加新订单来更新数据集 total>total 数据集.我必须跟踪 total 数据集的变化因此数据集 total 应该如下所示:

My objective is that I need to update the dataset total with the dataset have_1 and have_2 in a loop. When I start with have_1, a message=A implies that I have to update the dataset total by simply adding a new order to the total dataset. I must keep track the changes in the total datasets Hence the dataset total should look like this:

order_num time price qty id;
1 34197 11 550 1
2 34198 10.5 450 1
3 34199 10 500 1

然后，数据集 total 需要用数据集 have_2 进行更新，其中 message=B 意味着 有更新>qty 到 order_num 已经在 total 数据集中.我必须通过删除一些 qty 来更新 order_num=2.因此，total 数据集应如下所示:

Then, the dataset total needs to be updated with the dataset have_2 where message=B implies that there is an update the qty to an order_num that is already in the the total datasets. I have to update the order_num=2 by removing some of the qty. Hence, the total dataset should look like this:

order_num time price qty id;
1 34197 11 550 2
2 34198 10.5 350 2
3 34199 10 500 2

我有超过 1000 个 have_ 数据集，它们对应于另一个数据集中的每一行.重要的是我需要跟踪每个带有 id 的消息的 total 变化.假设我只有 have_1 和 have_2，那么这是我的暂定代码:

I have more than 1000 have_ datasets which corresponds to each row in a another datasets. What's important is that I need to keep track of the changes in total for every messages with an id. Assuming that I have only have_1 and have_2, then here's my tentative code:

%macro loop()
%do i=1 %to 2;

data total_temp;
set total; run;

data total_temp;
set have_&i;
if msg_type='A' then do;
set total have_&i;
drop message;
id=&i;
end;
if msg_type='B' then do;
merge total have_&i;
by order_num;
drop message;
qty=qty-delete_qty;
drop delete_qty;
id=&i
end;
run;

data total; set total_temp; run;

%end;
%mend;
%loop();

这段代码，比如在第一个循环之后，只保留一行对应于 have_1 中的内容.因此，我们可以在 then do 中使用 merge 和 set 命令吗?我必须使用的正确代码是什么?

This code, say after the first loop, keeps only one line which corresponds to what's in have_1. Hence, can we use a merge and a set command in a then do? What's the proper code that I have to use?

最终的数据集应如下所示:

The final datasets should look like this:

   order_num time price qty id;
    1 34197 11 550 1
    2 34198 10.5 450 1
    3 34199 10 500 1
    1 34197 11 550 2
    2 34198 10.5 350 2
    3 34199 10 500 2

推荐答案

您不需要在宏中执行此操作.您可以使用宏，但它会更慢.试试这个:

You don't need to do this in a macro. You CAN use a macro, but it will be slower. Try this:

data have_1;
input message $ order_num time price qty;
datalines;
A 3 34199 10 500 
run;

data have_2(index=(order_num));
input message $ order_num time delete_qty ;
datalines;
B 2 34200 100 
run;

data total(index=(order_num));
input order_num time price qty;
datalines;
1 34197 11 550
2 34198 10.5 450
run;

/*First, add new orders*/
proc append base=total data=have_1(where=(message="A")) force;
run;

/*Now update for the deletions*/
data total;
modify total have_2(where=(message="B"));
by order_num;
qty = sum(qty,-delete_qty);
drop message delete_qty;
run;

使用 PROC APPEND 将新订单附加到总数据集.这会维护索引并允许您通过 MODIFY 语句进行更新.

Append the new order to the total data set with PROC APPEND. This maintains the index and allows you to do the update through the MODIFY statement.

这可以通过两个修改语句来完成，不过我发现通过 append 添加新记录更清晰.

This could be done through two modify statements, though I find adding the new records through append to be clearer.

这篇关于SAS:合并执行命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！