BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasnewbiexx
Calcite | Level 5

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

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;

View solution in original post

4 REPLIES 4
ChrisBrooks
Ammonite | Level 13

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?

ChrisNZ
Tourmaline | Level 20

@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.  

ChrisNZ
Tourmaline | Level 20

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;
sasnewbiexx
Calcite | Level 5
Thank you very much, guys! The suggestion @ChrisNZ worked fined for me.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 3097 views
  • 0 likes
  • 3 in conversation