|
View previous topic :: View next topic
|
| Author |
Message |
rajesh1183
New User
Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad
|
|
|
|
Hi ,
I have a below requirement
Requirement:-
I have 2 tables, say table A and table B. I have to update a column in table A with a column in table B based on the matching criteria. If the matching criteria fails, then I have to retain the value that is already present. Else I have to update the table A with the value present in table B
...
Below is the code that I tried
| Code: |
UPDATE STUDENT_TAB A
SET GRADE = (SELECT CASE
WHEN B.GRADE IS MISSING
THEN A.GRADE
ELSE B.GRADE
END
FROM GRADE_TAB B
WHERE B.STUD_NO = A.STUD_NO)
|
Input data:
| Code: |
STUDENT_TAB
-----------------
STUD_NO GRADE
111 A
222 B
333 ==> SPACES IS ALSO VALID
444 D
GRADE_TAB
--------------
STUD_NO GRADE
111 X
222 B
555 Z
|
Expected result:-
------------------
| Code: |
STUDENT_TAB
STUD_NO GRADE
111 X
222 B
333 ==> SPACES IS ALSO VALID
444 D
|
but receiving the following result
| Code: |
STUDENT_TAB
STUD_NO GRADE
111 X
222 B
333 ==> SPACES IS ALSO VALID
444
|
I want to implement this query in SAS. I am unable to handle the missing/null values returned in the sub-query... can anyone assist me...??
TIA.... |
|
| Back to top |
|
 |
PeterHolland
Global Moderator
Joined: 27 Oct 2009
Posts: 2481
Location: Netherlands, Amstelveen
|
|
|
|
COALESCE (column-name <, ... column-name>) could probably help you with that.
COALESCE accepts one or more column names of the same data type. The COALESCE function checks the value of each column in the order in which they are listed and returns the first nonmissing value. If only one column is listed, the COALESCE function returns the value of that column. If all the values of all arguments are missing, the COALESCE function returns a missing value. |
|
| Back to top |
|
|
rajesh1183
New User
Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad
|
|
|
|
I have modified my query as I assume that my above query will not work properly...
| Code: |
PROC SQL;
UPDATE STUDENT_TAB A
SET GRADE = CASE WHEN (SELECT B.GRADE INTO :BGRADE
FROM GRADE_TAB B
WHERE B.STUD_NO = A.STUD_NO)
IS MISSING
THEN A.GRADE
ELSE "&BGRADE"
END
;
QUIT;
|
And I am receiving the following warning message in SAS log
| Code: |
WARNING: INTO clause that is not in the outermost query block will be ignored.
WARNING: Character expression will be truncated when assigned to character column GRADE.
|
looks like the macro variable &BGRADE is not resolved properly.... Any clue where it went wrong... |
|
| Back to top |
|
|
rajesh1183
New User
Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad
|
|
|
|
Just came to know the below info...
| Quote: |
Use the INTO clause only in the outer query of a SELECT statement and not in a subquery.
|
Any idea on using the returned value(B.GRADE) for UPDATE...?? |
|
| Back to top |
|
|
rajesh1183
New User
Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad
|
|
|
|
| Thank You Peter, looks like COALESCE has worked... intial results are OK. I have to verify by loading some huge no.of records... thanks alot... |
|
| Back to top |
|
|
rajesh1183
New User
Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad
|
|
|
|
Also, can you assist me in writing multiple column update as below in SAS..
| Code: |
UPDATE TABLE_A A
SET (COL1,COL2,COL3) = SELECT B.COL1,B.COL2,B.COL3 FROM TABLE_B B
;
|
I am receiving syntax errors when written in above format.. is this way of writing the query allowed in SAS/PROC SQL ..?? |
|
| Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8697
Location: Dubuque, Iowa, USA
|
|
|
|
| Quote: |
| is this way of writing the query allowed in SAS/PROC SQL ..?? |
What does the manual tell you? SAS has their documentation online, and they have a PROC SQL manual. |
|
| Back to top |
|
|
rajesh1183
New User
Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad
|
|
|
|
from the syntax, I think, the above requested query is not possible
| Code: |
UPDATE table-name|sas/access-view|proc-sql-view <AS alias> SET column=sql-expression
<, ... column=sql-expression>
<SET column=sql-expression
<, ... column=sql-expression>>
<WHERE sql-expression>;
|
|
|
| Back to top |
|
|
|
|
