CODEWITHSUNDEEP
sqlmergesas
FrankTan
FrankTan

Reputation: 1686

similar statement to merge sql in sas

hi guys i need to merge two tables in Sas

but with a condition like in a sql merge statement:

MERGE INTO TABLE_NAME USING table_reference ON (condition)
WHEN MATCHED THEN
UPDATE SET column1 = value1 [, column2 = value2 ...]
WHEN NOT MATCHED THEN
INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...

there is somthing like that?

in case there isn't can you tell me how can i make this kind of append with condition ?

Upvotes: 1

Views: 1394

Answers (2)

Longfish
Longfish

Reputation: 7602

You can use MODIFY to insert new rows as well as replace existing data. The code will look something like this (I can't be more specific without knowing what you are matching on and any other conditions you may have). Just a word of warning, if both of your datasets are large then using MODIFY in this way is very inefficient as a separate lookup is performed against the master dataset for each row in the transaction dataset. Using an index, as in @Rob's solution, will improve performance in this instance. The alternatives, as @Rob says, are to use UPDATE (similar to MODIFY here) or just a standard MERGE, both of which replace the existing dataset rather than modifying in place. A final option is to use PROC SQL where you can use something similar to your current code.

data master;
modify master (in=a) transaction (in=b);
by id;
if not a and <condition> then do;
    _error_=0;
    output;
    end;
else if a and b and <condition> then replace;
run;

Upvotes: 1

Robert Penridge
Robert Penridge

Reputation: 8513

I'm not aware of any straightforward way of doing this. When I have done it in the past I first appended new rows (using proc append) and then had a second step to modify existing rows. For the modify step you need to use a combination of a datastep with a set and a modify statement in a single datastep if you want to rebuild the table in-place. The advantage of doing this is that it's a lot less I/O (if it's a big table and only a small subset to update) and it keeps existing indexes. The downside is it's a lot more complicated. The code looks something like this:

  **
  ** REPLACE VALUES IN CPANEL.PW_STAT WITHOUT REBUILDING THE ENTIRE TABLE.
  *;
  data cpanel.pw_stat;

    set redirect_updates;
    modify cpanel.pw_stat key=primary;

    select(_iorc_);  
      when(%sysrc(_sok)) do;
        * MATCHED TRANSACTION DATASET TO MASTER DATASET. REPLACE THE VALUE;
        if date_redirected ne tmp_date_redirected then do;
          date_redirected = tmp_date_redirected;
          replace;
        end;
      end;
      when(%sysrc(_dsenom)) do;
        * NO MATCH. DO NOT ADD OBS OR REPLACE OBS OR DELETE OBS.  RESET ERR AND DO NOTHING.;
        _error_ = 0;
      end;
      otherwise do;
        put 'ERROR: Unexpected value for _IORC_= ' _iorc_;
        put 'Program terminating. DATA step iteration # ' _n_;
        put _all_;
        stop;
      end;
    end;
  run;

I should add that if performance isn't an issue you are much better off just re-creating a table from scratch.

Upvotes: 0

Related Questions