SAS:合并在do命令中 [英] 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

然后，需要使用数据集have_2更新数据集total，其中message=B意味着将qty更新为order_num中已经存在的order_num.我必须通过删除某些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.

这可以通过两个Modify语句完成，尽管我发现通过append添加新记录更加清楚.

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

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