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