Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
SAS: Update query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> All Other Mainframe Topics
View previous topic :: :: View next topic  
Author Message
rajesh1183

New User


Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad

PostPosted: Thu Jan 21, 2010 7:14 pm    Post subject: SAS: Update query
Reply with quote

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
View user's profile Send private message

PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2442
Location: Netherlands, Amstelveen

PostPosted: Thu Jan 21, 2010 7:27 pm    Post subject:
Reply with quote

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
View user's profile Send private message
rajesh1183

New User


Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad

PostPosted: Thu Jan 21, 2010 7:43 pm    Post subject: Reply to: SAS: Update query
Reply with quote

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
View user's profile Send private message
rajesh1183

New User


Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad

PostPosted: Thu Jan 21, 2010 7:52 pm    Post subject:
Reply with quote

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
View user's profile Send private message
rajesh1183

New User


Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad

PostPosted: Thu Jan 21, 2010 8:30 pm    Post subject: Reply to: SAS: Update query
Reply with quote

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
View user's profile Send private message
rajesh1183

New User


Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad

PostPosted: Thu Jan 21, 2010 8:38 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8165
Location: East Dubuque, Illinois, USA

PostPosted: Thu Jan 21, 2010 8:54 pm    Post subject:
Reply with quote

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
View user's profile Send private message
rajesh1183

New User


Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad

PostPosted: Thu Jan 21, 2010 9:09 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> All Other Mainframe Topics All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us