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 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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