topic Re: proc sql update in SAS Programming

proc sql update

mariopellegrini — Wed, 27 Sep 2017 12:57:10 GMT

I can not understand why the next proc sql update values missing "var1" at c1 = 2 and c2 = 2 (update key). The resulting value is missing. I expected it to remain unchanged at 20

data A;
input c1 c2 var1 var2;
datalines;
1 1 12 32
1 2 32 21
2 1 12 98
2 2 20 30
2 3 11 11
3 1 21 22
4 1 31 32
2 4 22 22
6 1 77 77

;

data tmp;
input c1 c2 var1 var2;
datalines;
1 1 999 999
1 2 888 999
2 1 999 999
;
 
proc sql;
update  a 
   set var1=(select var1 from tmp as b
		         	where a.c1=b.c1 and a.c2=b.c2)

	where a.c1 in (select c1 from  tmp) and
          a.c2 in (select c2 from  tmp) 
		  ;

quit;

Re: proc sql update

Kurt_Bremser — Wed, 27 Sep 2017 13:16:37 GMT

update in SQL does not behave like update in a data step. Since your subquery produces a missing value, that missing value is set. Use the coalesce() function to keep the original value if the subquery yields no result.

Re: proc sql update

mariopellegrini — Wed, 27 Sep 2017 13:36:46 GMT

I do not understand, which missing value produces the subquery? at c1 = 1, c2 = 1 --- c1 = 1, c2 = 2 --- c1 = 2, c2 = 1 there are 3 lines corresponding to the values 12,32 - 32,21 - 12 98

Re: proc sql update

Kurt_Bremser — Wed, 27 Sep 2017 13:47:52 GMT

There is no observation in dataset tmp with c1=2 AND c2=2, so the subquery can't find a value for that combination, which is present in observation 4 of dataset A, where the value 20 is overwritten with the missing value from the subquery.

Re: proc sql update

mariopellegrini — Wed, 27 Sep 2017 14:01:59 GMT

ok, but all other combinations do not have values in the sub query (example for c1 = 3, c2 = 1). In these cases, however, the values of dataset A remain unchanged. Only for c1 = 2, c2 = 2 is updated to missing the value of var1

Re: proc sql update

Kurt_Bremser — Wed, 27 Sep 2017 14:08:25 GMT

Because your where condition at the end of the query:

where a.c1 in (select c1 from  tmp) and
          a.c2 in (select c2 from  tmp)

prevents the update from even touching those. But you do have at least one c1=2 and one c2=2 in tmp, so the combination of c1=2 and c2=2 is updated.

If you want to prevent this from happening, you best create a compound key (ie c3 = c1*100 + c2) in both datasets and join on that.

Re: proc sql update

mariopellegrini — Thu, 28 Sep 2017 15:14:46 GMT

ok I understand, thank you. but this would slow down the processing if it were large databases. Is not there a different method?

Re: proc sql update

Kurt_Bremser — Thu, 28 Sep 2017 16:04:56 GMT

When I know that I will need a specific derived value later, I set it when such a dataset is created.

Intelligent data makes for intelligent programs.