SAS: merge in a do command

Question

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;

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;

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

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

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();

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

Answer 1

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;

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.

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