topic Re: Proc SQL macro "skipping" updates in SAS Programming

Proc SQL macro "skipping" updates

anlynch — Wed, 20 Mar 2013 16:43:14 GMT

Hi,

I have written some code to update a table with various values from another table, however, SAS seems to randomly skip updating rows, even when the conditions at satisfied. I have noticed that if I restart SAS (close and re-open) the problem seems to be alleviated to some degree, but there will still missing values at times. I would say that this happens about 90% of the time I try and run the code. Furthermore, each time I run the code, (even with the same input data) the rows that don't get updated are different each time.





%if &i=1 %then %do;





proc sql feedback;




alter table l.raw_%sysfunc(date(),date7.)




add A num format best32.




add B num format best32.;




update l.raw_%sysfunc(date(),date7.) t1 




set A = (select ppg_total_&rpi2._a from l.raw_%sysfunc(date(),date7.)_copy where monotonic() = &i) where monotonic() = &i;




update l.raw_%sysfunc(date(),date7.) t1




set B = (select ppg_allow_total_&rpi2._a from l.raw_%sysfunc(date(),date7.)_copy where monotonic() = &i) where monotonic() = &i;




quit; 



%end;




%else %do;





proc sql;




update l.raw_%sysfunc(date(),date7.) t1




set A = (select ppg_total_&rpi2._a from l.raw_%sysfunc(date(),date7.)_copy where monotonic() = &i) where monotonic() = &i;




update l.raw_%sysfunc(date(),date7.) t1




set B = (select ppg_allow_total_&rpi2._a from l.raw_%sysfunc(date(),date7.)_copy where monotonic() = &i) where monotonic() = &i;




quit; 



%end;

Re: Proc SQL macro "skipping" updates

Astounding — Wed, 20 Mar 2013 17:44:17 GMT

I can't tell if this is the problem or not, but I believe that SAS considers the MONOTONIC function to be an undocumented, unsupported feature. It could be a source of problems.

Re: Proc SQL macro "skipping" updates

anlynch — Wed, 20 Mar 2013 17:47:16 GMT

yea, i had a feeling that monotonic might have something to do with it. i am trying to think of a solution to work around using monotonic.

Re: Proc SQL macro "skipping" updates

Reeza — Wed, 20 Mar 2013 18:45:12 GMT

It seems a bit unintuitive in SAS to update row by row like that as well. I've seen that done using cursors in SQL, but not in SAS.

It looks like straight join by row, and that A and B are set to the same thing? I'm not sure if you simplified the code to post it.

Re: Proc SQL macro "skipping" updates

anlynch — Wed, 20 Mar 2013 18:56:03 GMT

A and B are different variables, but the way in which they are retrieved are the same (one is pgg_total and one is ppg_allow).

the problem i am running into is getting sas to update only one row at a time... which is why i have two monotonic() constraints. the first retrieves the value from the table, and then the second only updates one row at a time.

edit: i am guessing i can figure out a better way to write this, without using monotonic() like this, but its intriguing to me that SAS evaluates it differently each time the code is executed.

Re: Proc SQL macro "skipping" updates

SASKiwi — Wed, 20 Mar 2013 18:57:58 GMT

Why not write your updating process in a DATA step where you have full row-by-row control and don't require undocumented features?

Re: Proc SQL macro "skipping" updates

anlynch — Wed, 20 Mar 2013 19:53:35 GMT

I tend to use sql as a crutch... more than I should.

Anyhow, I still kept the meat of the sql there, but instead of using monotonic i used a data step to create a macro variable to keep the constraints.

...and before I wrote this post, I didn't realize that monotonic was unsupported.

Re: Proc SQL macro "skipping" updates

SASKiwi — Wed, 20 Mar 2013 22:40:08 GMT

Understandible if you come from an SQL background, but SAS DATA step processing goes way beyond what you can do in SQL with the bonus of row-by-row control.

I've done SQL stored procedures with cursors to get row control but its way more complicated than DATA steps .

Re: Proc SQL macro "skipping" updates

Tom — Thu, 21 Mar 2013 00:51:32 GMT

Can you explain in non code what that program is trying to do?

I wouldn't normally use an "ALTER TABLE" statement in SAS. To add a column to a table would require SAS to totally recreate the table. So at that point why not just use a data step or an SQL create table statement to make the table you want?

	%if &i=1 %then %do;
		proc sql feedback;
		alter table l.raw_%sysfunc(date(),date7.)
		add A num format best32.
		add B num format best32.;
		update l.raw_%sysfunc(date(),date7.) t1
		set A = (select ppg_total_&rpi2._a from l.raw_%sysfunc(date(),date7.)_copy where monotonic() = &i) where monotonic() = &i;
		update l.raw_%sysfunc(date(),date7.) t1
		set B = (select ppg_allow_total_&rpi2._a from l.raw_%sysfunc(date(),date7.)_copy where monotonic() = &i) where monotonic() = &i;
		quit;
	%end;
	%else %do;
		proc sql;
		update l.raw_%sysfunc(date(),date7.) t1
		set A = (select ppg_total_&rpi2._a from l.raw_%sysfunc(date(),date7.)_copy where monotonic() = &i) where monotonic() = &i;
		update l.raw_%sysfunc(date(),date7.) t1
		set B = (select ppg_allow_total_&rpi2._a from l.raw_%sysfunc(date(),date7.)_copy where monotonic() = &i) where monotonic() = &i;
		quit;
	%end;