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-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

Register now!

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
  • 3769 views
  • 0 likes
  • 3 in conversation