sas-update

hi,
i am upating a table using table loader which works fine if i run very first time and populate
column as (| Emp: (aa, bb (00846XXX); aa, bb (07041XXX))

if i run it again this column gets dups as
(| Emp: (aa, bb (00846XXX); aa, bb (07041XXX))(| Emp: (aa, bb (00846XXX); aa, bb (07041XXX))

i don't want that if it sees | Emp: string don't update that column just skip it.



proc sql ;
         /* update existing records in the master table  */
         update QSMRTPDI.EXCP_FCT as m
            set EXCP_ADDTL_INFO_TEXT = (select EXCP_ADDTL_INFO_TEXT from &etls_lastTable as t
                                where m.ITEM_ID = t.ITEM_ID and
                                      m.EXCP_TYP_CDE = t.EXCP_TYP_CDE and
                                      m.WC_ID = t.WC_ID and
                                      m.SVCTKT_NUM = t.SVCTKT_NUM and
                                      m.EXCP_EVT_DTE = t.EXCP_EVT_DTE and
                                      m.EXCP_EVT_TME = t.EXCP_EVT_TME)
               where ITEM_ID in (select distinct ITEM_ID from &etls_lastTable) and
                        EXCP_TYP_CDE in (select distinct EXCP_TYP_CDE from &etls_lastTable) and
                        WC_ID in (select distinct WC_ID from &etls_lastTable) and
                        SVCTKT_NUM in (select distinct SVCTKT_NUM from &etls_lastTable) and
                        EXCP_EVT_DTE in (select distinct EXCP_EVT_DTE from &etls_lastTable) and
                        EXCP_EVT_TME in (select distinct EXCP_EVT_TME from &etls_lastTable)
         ;
         
      quit;
sam2929Asked:
Who is Participating?
 
wshark83Commented:
yes you can use the index function index(EXCP_ADDTL_INFO_TEXT,"| Emp:") = 0
0
 
wshark83Commented:
i assume "| Emp: " is in EXCP_ADDTL_INFO_TEXT field

then you can add an additional and statement to say

substr(EXCP_ADDTL_INFO_TEXT,1,6) ne "| Emp:"
0
 
sam2929Author Commented:
yes we can but it can be 1,6 i not fixed can we use index stmt
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.