updating a table in sql with multiple columns

[alepage]

Hello,

I have found an example on the web how to update a table (see code below) 

UPDATE dest_tab tt
SET    (tt.code, tt.description) = (SELECT st.code, st.description
                                    FROM   source_tab st
                                    WHERE  st.id = tt.id)
WHERE  EXISTS (SELECT 1
               FROM   source_tab
               WHERE  id = tt.id);

I have try something similar using sashelp.class, but it is not working.

Could someone help me with that.  The error I am getting is: 

28 PROC SQL;
29 update dest_tab as tt
30 set tt.Name= (select Name from source_tab as st)
_
73
76
ERROR 73-322: Expecting an =.

ERROR 76-322: Syntax error, statement will be ignored.

data dest_tab;
set sashelp.class;
stop;
run;
data dest_tab;
do _n_ = 1 to 19;
id = _n_ ;
output;
end;
set dest_tab;
run;


proc contents data=sashelp.class;
run;

/* Variables in sashelp.class are:
Name, Sex, age, Height, Weight */

proc contents data=dest_tab;
run;

data source_tab;
set sashelp.class;
 id=_n_;
run;

proc sql;
UPDATE dest_tab tt
SET    (tt.Name, tt.Sex, tt.Age,tt.Height, tt.Weight)  = 
(SELECT st.Name, st.Sex, st.Age, st.Height, st.Weight
                                    FROM   source_tab st
                                    WHERE  st.id = tt.id)
WHERE  EXISTS (SELECT 1
               FROM   source_tab
               WHERE  id = tt.id);
quit;

[PaigeMiller]

When you display the log as plain text

28 PROC SQL;
29 update dest_tab as tt
30 set tt.Name= (select Name from source_tab as st)
_
73
76
ERROR 73-322: Expecting an =.

ERROR 76-322: Syntax error, statement will be ignored.

the spacing is altered, and so the error indicator under the code appears left justified. If you copy the log and paste it into the </> window, then this problem goes away, the error indicator is placed under the character of the code where the problem was found. This makes the log much more readable and understandable. Please do this, in this thread, and in all future threads the log must be (not optional) presented this way.

[alepage]

1                                                          The SAS System                           09:07 Thursday, November 3, 2022

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Programme';
4          %LET _CLIENTPROCESSFLOWNAME='Flux de processus';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=SVG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         %macro HTML5AccessibleGraphSupported;
15             %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
16         %mend;
17         FILENAME EGHTML TEMP;
18         ODS HTML5(ID=EGHTML) FILE=EGHTML
19             OPTIONS(BITMAP_MODE='INLINE')
20             %HTML5AccessibleGraphSupported
21             ENCODING='utf-8'
22             STYLE=HTMLBlue
23             NOGTITLE
24             NOGFOOTNOTE
25             GPATH=&sasworklocation
26         ;
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
27         
28         data dest_tab;
29         length id 8.;
30         set sashelp.class;
31         stop;
32         run;

NOTE: Variable id is uninitialized.
NOTE: There were 1 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.DEST_TAB has 0 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

33         data dest_tab;
34         do _n_ = 1 to 19;
35         id = _n_ ;
36         output;
37         end;
38         set dest_tab;
39         run;

NOTE: There were 0 observations read from the data set WORK.DEST_TAB.
NOTE: The data set WORK.DEST_TAB has 19 observations and 6 variables.
NOTE: Compressing data set WORK.DEST_TAB increased size by 100.00 percent. 
      Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
2                                                          The SAS System                           09:07 Thursday, November 3, 2022


40         
41         /* Variables in sashelp.class are:
42         Name, Sex, age, Height, Weight */
43         
44         proc contents data=dest_tab;
45         run;

NOTE: PROCEDURE CONTENTS used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
      

46         
47         data source_tab;
48         set sashelp.class;
49          id=_n_;
50         run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.SOURCE_TAB has 19 observations and 6 variables.
NOTE: Compressing data set WORK.SOURCE_TAB increased size by 100.00 percent. 
      Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
      

51         
52         proc sql;
53         UPDATE dest_tab tt
54         SET    (Name, Sex, Age,Height, Weight)  =
                  _
                  22
                  76
ERROR 22-322: Expecting a name.  

ERROR 76-322: Syntax error, statement will be ignored.

55         (SELECT Name, Sex, Age, Height, Weight
56                                             FROM   source_tab st
57                                             WHERE  st.id = tt.id)
58         WHERE  EXISTS (SELECT 1
59                        FROM   source_tab
60                        WHERE  id = tt.id);
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
61         quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
62         
63         %LET _CLIENTTASKLABEL=;
64         %LET _CLIENTPROCESSFLOWNAME=;
65         %LET _CLIENTPROJECTPATH=;
66         %LET _CLIENTPROJECTPATHHOST=;
67         %LET _CLIENTPROJECTNAME=;
3                                                          The SAS System                           09:07 Thursday, November 3, 2022

68         %LET _SASPROGRAMFILE=;
69         %LET _SASPROGRAMFILEHOST=;
70         
71         ;*';*";*/;quit;run;
72         ODS _ALL_ CLOSE;
73         
74         
75         QUIT; RUN;
76         

[PaigeMiller]

So the error appears to be the parenthesis in the SET clause. This is consistent with what the SAS documentation shows:

UPDATE table-name | sas/access-view | proc-sql-view <AS alias>

SET column-1=sql-expression-1 <, column-2=sql-expression-2, ...>

<WHERE sql-expression>;

[PGStats · Accepted solution]

With SAS/SQL you have to use the inefficient syntax:

proc sql;
UPDATE dest_tab tt
SET 
	Name = (SELECT Name FROM source_tab WHERE id = tt.id) ,
	Sex = (SELECT Sex FROM source_tab WHERE id = tt.id) ,
	Age = (SELECT Age FROM source_tab WHERE id = tt.id) ,
	Height = (SELECT Height FROM source_tab WHERE id = tt.id) ,
	Weight = (SELECT Weight FROM source_tab WHERE id = tt.id) 
WHERE  EXISTS (SELECT *
               FROM   source_tab
               WHERE  id = tt.id);
quit;