topic Re: update dataset clauses in one proc sql statement in SAS Programming

update dataset clauses in one proc sql statement

rasmuslarsen — Wed, 24 May 2017 13:21:36 GMT

I am trying to combine the two update statements into one.

The fist clause:

proc sql;
update dataset
set var1 = 
case 
	when var1 is missing and filter_var = 1  then "new value"
	else var1
	end 
;quit;

The second clause:

proc sql;
update dataset
set var2 = 
case 
when var2 is missing and filter_var = 1 then "new value"
else var2
end 
;quit;

My try at combining the two clauses into one:

proc sql;
update dataset
set var1 = 
case 
when var1 is missing and filter_var = 1 then "new value"
else var1
end,
set var2 = 
case 
when var2 is missing and filter_var = 1 then "new value"
else var2
end 
;quit;

However I get an error, expecting a ",".

Any ideas if this is possible?

Re: update dataset clauses in one proc sql statement

Reeza — Wed, 24 May 2017 13:27:56 GMT

I believe the syntax is

SET <list of changes>

Try removing the second SET.

Re: update dataset clauses in one proc sql statement

Kurt_Bremser — Wed, 24 May 2017 13:30:13 GMT

Try

proc sql;
update dataset
set
  var1 = case 
    when var1 is missing and filter_var = 1 then "new value"
    else var1
  end,
  var2 = case 
    when var2 is missing and filter_var = 1 then "new value"
    else var2
  end 
;
quit;

Either omit the second "set", or the comma immediately before it.

See the syntax description in http://support.sas.com/documentation/cdl/en/sqlproc/69822/HTML/default/viewer.htm#p0ci36zwxhm1xdn1a943yeczfalk.htm