I have a table with a dummy var set to 0 for all rows EXCEPT the rows most recently sent for each person. In that case the dummy var should be set to 1.
The dummy var is incorrectly set to 1 for more than just the most recently sent row so I need to reset the dummy var to zero for all rows EXCEPT the rows most recently sent by each person.
I have a var called report_time and can ID the most recently sent row by using max(report_time).
The following syntax did not work:
set dummy_var =
when max(report_time) = report_time then dummy_var=1
else dummy_var = 0
group by person
order by report_time desc, person asc;
below end is the following:
ERROR: The value expression referenced by column dummy_var can not directly contain a summary function.
Below group is the following:
ERROR 180-322: Statement is not valid or it is used out of proper order.
I tested the timing of doing a proc sql update of the rows that needed to be changed from dummy_var = 1 to dummy_var = 0, and found that takes over an hour which is much too long.
The next approach I tested was to delete the rows that needed updating, then adding those rows back in but with the correction.
I was surprised to find it takes 10 times longer to delete rows using proc sql than it takes using a data step.
Using proc sql insert is very quick so that is the method I used to get the rows of data back into the dataset.
I am trying to update an oracle table using base SAS in the most efficient manner and welcome any suggestions.
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.