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!
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;
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?
@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.
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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.