Thanks for the details Tom. I will explore DS2 ... View more

Thanks Jaap. I will try to get familiarized with DS2 ... View more

ok...but due to some constraints, I am not supposed to alter the column data type. That's the reason why I decided to convert back. ... View more

Thanks a lot for the details Tom. Yes, the column is an identifier for customer so I will try out the option for converting to character while extracting into SAS and reconverting it into numeric and storing in sas. ... View more

Thanks a lot for the details Jaap. I will check out the DS2 option ... View more

Thanks a lot Patrick. I will try this out and post the result. Regards, Sheeba Swaminathan ... View more

Hi Patrick, I got your point. I also want to specify that I need to update table A based on comparison....My result table is again going to be A. No new table needs to be created. I am reposting the corrected sample data below..... Table A line_id (PK)    groupid        element        pf           scope         pid      1                                      ABC           pf1           scope1     pid1       2                                      DEF           pf2           scope2      pid2      3                                      XYZ           pf3           scope3      pid3 Table B groupid       pf      scope             pid     element 201           pf1      scope1          pid1         ABC 202            pf2      scope2           pid2       DEF 203           pf3      scope3          pid3         XYZ 204                  pf4     scope4     pid4       XYZ ) for the first record A.element = ABC and A.pf= B.pf so I need to assign A.groupid=B.group id 2) for the second record A.element='DEF' then do;   if(A.scope=B.scope and A.pid=B.pid ) then A.groupid=B.groupid. 3) for the third record A.element='XYZ' then do;                     if(A.scope=B.scope ) then A.groupid=B.groupid. Output TABLE A line_id (PK)    groupid        element        pf           scope         pid      1                201                      ABC           pf1           scope1     pid1       2               202                       DEF           pf2           scope2      pid2      3                203                      XYZ           pf3           scope3      pid3 ... View more

Hi Patrick, Thanks for the reply. Apologies for incorrect data. .....group id can have only unique values or null values.....Also if the element is xyz the condition is different. Yes here i need to harcode the values. Table A line_id (PK)    groupid        element        pf           scope         pid      1                                      ABC           pf1           scope1     pid1       2                                      DEF           pf1           scope1      pid1      3                                      XYZ           pf2           scope1      pid2      4                                      ABC          pf3           sope2      pid3          5                                      DEF           pf4           scope2      pid4 Table B groupid       pf      scope             pid 201           pf1      scope1          pid1 202            pf1      scope1           pid1 203           pf2      scope1           pid2                    pf3     scope2           pid3 204             pf4      scope2           pid4 The update condition here is      if A.element = "ABC", then update A.groupid = (Select groupId from B where A.pf = b.pf)     if A.element = "DEF", then update A.groupId =  (Select groupId from B where A.scope = B.scope and A.pid=B.pid) My aim is to update A comparing the values of A and B. Initially Table A does not have group idd. After the transformation A would look like ouptut Table A line_id (PK)    groupid  element        pf           scope         pid      1                                      ABC      pf1           scope1     pid1       2                                     DEF      pf1           scope1      pid1      3                                      XYZ      pf2           scope1      pid2      4                                      ABC      pf3           scope2      pid3      5                                      DEF      pf4           scope2      pid4 1) for the first record A.element = ABC and A.pf= B.pf so I need to assign A.groupid=B.group id 2) for the second record A.element='DEC' then do;   if(A.scope=B.scope and A.pid=B.pid ) then A.groupid=B.groupid. 3) for the third record A.element='YZ' then do;                     if(A.scope=B.scope ) then A.groupid=B.groupid. 4) Re: DI Query ... View more

Hi Linush, Please find the sample input and output data below. I have a table A having following columns Table A line_id (PK)    groupid        element        pf           scope         pid      1                                      ABC           pf1           scope1     pid1       2                                      DEF           pf1           scope1      pid1      3                                      XYZ           pf2           scope1      pid2      4                                      XXX           pf3           sope2      pid3      5                                      YYY           pf4           scope2      pid4 Table B  groupid       pf      scope             pid 201           pf1      scope1          pid1 202            pf1      scope1           pid1 201           pf2      scope1           pid2                    pf3     scope2           pid3 203             pf4      scope2           pid4 The update condition here is      if A.element = "ABC", then update A.groupid = (Select groupId from B where A.pf = b.pf)     if A.element = "DEF", then update A.groupId =  (Select groupId from B where A.scope = B.scope and A.pid=B.pid) My aim is to update A comparing the values of A and B. Initially Table A does not have group idd. After the transformation A would look like ouptut Table A line_id (PK)    groupid  element        pf           scope         pid      1                                      ABC      pf1           scope1     pid1       2                                     DEF      pf1           scope1      pid1      3                                      XYZ      pf2           scope1      pid2      4                                      ABC      pf3           scope2      pid3      5                                      DEF      pf4           scope2      pid4 1) for the first record A.element = ABC and A.pf= B.pf so I need to assign A.groupid=B.group id 2) for the second record A.element='DEC' then do;   if(A.scope=B.scope and A.pid=B.pid ) then A.groupid=B.groupid. ... View more

Hi Patrick, I am sorry about the disconnect here. I wanted to rephrase my question to make it more clear with examples I have a table A having following columns 1) line_id (PK)  2)groupid  3) element 4) pf 5) scope 6)pid All i wanted here is to update table A's groupid column based on matching attributes in table B. The structure of Table B  is 1) execkey(PK) 2)groupid 3)pf 4)scope 5)pid The update condition here is      if A.element = "ABC", then update A.groupid = (Select groupId from B where A.pf = b.pf)      if A.element = "DEF", then update A.groupId =  (Select groupId from B where A.scope = B.scope and A.pid=B.pid) Regards, Sheeba Swaminathan ... View more

Hi Linush, Both Table A and B are registered in the metadata. A                                                                                          B groupid  element pf                                                      group id element pf I would like to compare the element and pf of both the tables A and B. if both match, i want to assign A.gorupid= B.group id. If the columns are not matching, I want to assign a new value to A.groupid using surrogate key generator. Regards, Sheeba Swaminathan ... View more

Hi Patrick, I am using DIS 4.6. I will try out the update transformation Regards, Sheeba Swaminathan ... View more

Hi Linush, I am sorry about that...It was a mistake...I meant column. I would like to compare 2 Columns of  two tables A and B. If the column values match, I need to update table A. How do I do that with SAS DI studio? Regards, Sheeba Swaminathan ... View more