topic Re: How to reset all values of a dummy var to zero except for rows with max(report_time) per person in SAS Procedures

How to reset all values of a dummy var to zero except for rows with max(report_time) per person

BLE — Mon, 23 Jan 2017 17:41:10 GMT

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.

Re: How to reset all values of a dummy var to zero except for rows with max(report_time) per person

Shmuel — Mon, 23 Jan 2017 18:01:20 GMT

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;

Re: How to reset all values of a dummy var to zero except for rows with max(report_time) per person

BLE — Mon, 23 Jan 2017 19:42:04 GMT

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?

Re: How to reset all values of a dummy var to zero except for rows with max(report_time) per person

Shmuel — Mon, 23 Jan 2017 19:50:32 GMT

Sorry, I have almost no experience with oracle tables.

Re: How to reset all values of a dummy var to zero except for rows with max(report_time) per person

BLE — Mon, 30 Jan 2017 14:39:25 GMT

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.

Re: How to reset all values of a dummy var to zero except for rows with max(report_time) per person

BLE — Mon, 30 Jan 2017 14:40:03 GMT

Re: How to reset all values of a dummy var to zero except for rows with max(report_time) per person

Peter_C — Wed, 01 Feb 2017 09:31:51 GMT

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