BookmarkSubscribeRSS Feed
BLE
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 =

case

when max(report_time) = report_time then dummy_var=1

else dummy_var = 0

end;

group by person

order by report_time desc, person asc;

quit;

 

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.

6 REPLIES 6
Shmuel
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;

run;

BLE
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?
Shmuel
Garnet | Level 18

Sorry, I have almost no experience with oracle tables.

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

 

 

Peter_C
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
PeterC

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 1355 views
  • 2 likes
  • 3 in conversation