DATA Step, Macro, Functions and more

update dataset clauses in one proc sql statement

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

update dataset clauses in one proc sql statement

 

 

 

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?

 


Accepted Solutions
Solution
‎05-30-2017 03:22 AM
Super User
Posts: 7,761

Re: update dataset clauses in one proc sql statement

Posted in reply to rasmuslarsen

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#p0ci36zwxhm1xdn1a9...

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 19,770

Re: update dataset clauses in one proc sql statement

Posted in reply to rasmuslarsen

I believe the syntax is 

 

SET <list of changes>

 

Try removing the second SET. 

Solution
‎05-30-2017 03:22 AM
Super User
Posts: 7,761

Re: update dataset clauses in one proc sql statement

Posted in reply to rasmuslarsen

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#p0ci36zwxhm1xdn1a9...

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 104 views
  • 1 like
  • 3 in conversation