Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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: 2429
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: 7995
Location: Bellevue, IA

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 JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts IMS DB-How to update a record (a sing... Nic Clouston IMS DB/DC 9 Thu Mar 09, 2017 4:38 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us