BookmarkSubscribeRSS Feed
Obsidian | Level 7 BLE
Obsidian | Level 7

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:

proc sql;

update table1

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.

Garnet | Level 18

You can do it easyly by sort and a data step:


proc sort data=have; by person_id report_time; run;

data want;

  set have;

       by person_id;

           if last.person_id then dummy_var=1;

           else dummy_var=0;


Obsidian | Level 7 BLE
Obsidian | Level 7
The dataset that needs to be updated is an oracle table. Will it process slower using a sort and data step? Can it be done given the data and the set are the same and are an oracle table?
Garnet | Level 18

Sorry, I have almost no experience with oracle tables.

Obsidian | Level 7 BLE
Obsidian | Level 7

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.



Rhodochrosite | Level 12
Explicit pass-thru
This allows you to send oracle code from SAS
Once you have an oracle update working, then consider how SAS would need to achieve the same thing.... it is not always obvious.
Good Luck



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
  • 6 replies
  • 3 in conversation