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