I have the code as below
data master;
input id name $ status $;
datalines;
1 B b
2 C c
3 A a
;;;;
run;
PROC SQL;
ALTER TABLE master
ADD PRIMARY KEY (id);
QUIT;
data transaction;
input name $ status $;
datalines;
A f
F f
E e
D d
B z
C x
;;;;
run;
proc sort data = master;
by name;
run;
proc sort data = transaction;
by name;
run;
I would like to merge the transaction dataset to master dataset, and update the value from master with value from transaction dataset. To achieve this, I could use the code below
data have;
retain _maxID;
merge have addon;
by name;
if id = . then id = _maxID + 1;
_maxID = max(id, _maxID);
run;
The result would look like this
id name status
3 A f
1 B z
2 C x
4 D d
5 E e
6 F f
However, the master dataset is reset, and I lost the primary key constraint on id column of the master dataset.
As far as I know, merge, set and update command create new dataset, not update the current dataset.
The modify statement is the only one that update the current dataset, but replacing the merge statement from above code with modify does not work.
I could solve this problem by reinstate the primary constraint on the master dataset after merging, but it is not a good solution imo.
Is there any other way than this? Help is appreciated and thanks in advance.
As you said, the only statement that doesn't create a new dataset is MODIFY, but you can use this to achieve your goal. There is a good SUGI paper on using MODIFY here
Here is the code you can use, I've set ID to equal the current iteration which works in this example, but I would check it is suitable for your actual purpose.
data master;
modify master transaction;
by name;
select (_IORC_);
when (%sysrc(_SOK)) replace;
when (%sysrc(_DSENMR)) do;
id=_n_;
output;
_error_=0;
end;
otherwise;
end;
run;
There is a similar answered question here that gives additional detail.