topic Re: Overwrite or insert rows into new table with PROC SQL in SAS Procedures

Overwrite or insert rows into new table with PROC SQL

sasnewbiexx — Thu, 31 Jan 2019 10:18:20 GMT

Hey guys!

I currently have a programme that generates new records every Sunday for a certain period. Two of the columns in the table are PERIOD_START and PERIOD_END. I want the programme to add the new weeks' records into another table after every run.

Currently I use INSERT INTO in PROC SQL, which works fine, however if I have a change in source data and need to re-run the programme again mid-week(not a Sunday) it creates duplicate records in my destination table. How can I re-write the PROC SQL so the query first checks if the PERIOD_START and PERIOD_END dates from the new table match in the destination table and:

1. If they do - overwrite the rows with the new data

2. If they don't - just execute the insert into.

Cheers!

Re: Overwrite or insert rows into new table with PROC SQL

ChrisBrooks — Thu, 31 Jan 2019 23:40:23 GMT

Hi @sasnewbiexx and welcome to the community.

Do you need to use Proc SQL as a data step merge would be much simpler - automatically overwriting records with matching period_start and period_end values and inserting those which don't?

Re: Overwrite or insert rows into new table with PROC SQL

ChrisNZ — Fri, 01 Feb 2019 02:37:45 GMT

@ChrisBrooks 's suggestion is very straight forward, and also presents the advantage that the table remains sorted (if it's a SAS data set) since you merge BY.

The downside is that you recreate the whole table, so it is wasteful if you want to append a row at the end of a 100m-row table.

In this case the MODIFY statement might be better suited.

Re: Overwrite or insert rows into new table with PROC SQL

ChrisNZ — Fri, 01 Feb 2019 03:34:32 GMT

MODIFY works like this:


data MASTER_TABLE(index=(X)); 
  retain Y 1;       
  do X=1 to 1e7;
    output; 
  end;   
run;

data UPDATE_TABLE;
  retain Y 2;       
  do X=0 to 1;
    output; 
  end;   
run;
        
data MASTER_TABLE;
  modify MASTER_TABLE 
         UPDATE_TABLE ;
  by X;
  select (_IORC_);
    when (%sysrc(_sok))  replace;  %* Update observation;
    when (%sysrc(_dsenmr)) do;     %* Add observation;
      output;  
      _ERROR_=0;
    end;
    otherwise do;                  %* Unexpected value;
      putlog 'An unexpected I/O error has occurred.';  
      stop;
    end; 
  end; 
run;

Re: Overwrite or insert rows into new table with PROC SQL

sasnewbiexx — Mon, 04 Feb 2019 07:45:28 GMT

Thank you very much, guys! The suggestion @ChrisNZ worked fined for me.