Yes, if I use equal sign it works, problem is need to use like operator. ... View more

Just wrote to another location, and the same error 26  RSUBMIT; NOTE: Remote submit to GIDWSAS commencing. 599  OPTIONS FULLSTIMER MSGLEVEL=i; 600  proc sql; 601  create  table marts.rule_explode as 602  SELECT  MI.*, RT.* 603  FROM MAP_INPUT AS MI 604  INNER JOIN rule_trans3 AS RT 605  ON   MI.AAL_SRC_ACC  LIKE   trim(RT.s_account) 606  AND  MI.AAL_SRC_ACC  NOT LIKE trim(RT.n_account) 607  AND  MI.AAL_SRC_CO   LIKE     trim(RT.s_COMPANY_CD) 608  and  MI.AAL_SRC_CO   not LIKE trim(RT.n_COMPANY_CD) 609  AND  MI.AAL_SRC_BR   LIKE     trim(RT.s_BRANCH) 610  and  MI.AAL_SRC_BR   not LIKE trim(RT.n_BRANCH) 611  AND  MI.AAL_SRC_PROD LIKE     trim(RT.s_PRODUCT) 612  AND  MI.AAL_SRC_PROD NOT LIKE trim(RT.n_PRODUCT) 613  AND  MI.AAL_SRC_SUB  LIKE     trim(RT.s_SUBSYS) 614  AND  MI.AAL_SRC_SUB  NOT LIKE trim(RT.n_SUBSYS) 615  AND  MI.POST_DATE = RT.POST_DATE 616  ; WARNING: Variable POST_DATE already exists on file MARTS.RULE_EXPLODE. NOTE: The SAS System stopped processing this step because of errors. NOTE: There were 10223 observations read from the data set WORK.MAP_INPUT. NOTE: There were 3998 observations read from the data set WORK.RULE_TRANS3.       WHERE POST_DATE='20150820'; WARNING: The data set MARTS.RULE_EXPLODE may be incomplete.  When this step was stopped there were 21849 observations and 63 variables. NOTE: PROCEDURE SQL used (Total process time):       real time           31.36 seconds       user cpu time       30.44 seconds       system cpu time     0.78 seconds       memory              1826230.53k       OS Memory           2097020.00k       Timestamp           27/08/2015 08:26:51 AM       Step Count                        38  Switch Count  3       Page Faults                       0       Page Reclaims                     471691       Page Swaps                        0       Voluntary Context Switches        26       Involuntary Context Switches      3210       Block Input Operations            37520       Block Output Operations           50136 617  quit; NOTE: Remote submit to GIDWSAS complete. ... View more

Ran the same in the two servers with the fullstimer options, below is the tow outputs. Looks to me a memory issue, not sure what should solve it. NEW SERVER OUPUT: 524  proc sql; 525  create  table rule_explode as 526  SELECT  MI.*, RT.* 527  FROM MAP_INPUT AS MI 528  INNER JOIN rule_trans3 AS RT 529  ON   MI.AAL_SRC_ACC  LIKE   trim(RT.s_account) 530  AND  MI.AAL_SRC_ACC  NOT LIKE trim(RT.n_account) 531  AND  MI.AAL_SRC_CO   LIKE     trim(RT.s_COMPANY_CD) 532  and  MI.AAL_SRC_CO   not LIKE trim(RT.n_COMPANY_CD) 533  AND  MI.AAL_SRC_BR   LIKE     trim(RT.s_BRANCH) 534  and  MI.AAL_SRC_BR   not LIKE trim(RT.n_BRANCH) 535  AND  MI.AAL_SRC_PROD LIKE     trim(RT.s_PRODUCT) 536  AND  MI.AAL_SRC_PROD NOT LIKE trim(RT.n_PRODUCT) 537  AND  MI.AAL_SRC_SUB  LIKE     trim(RT.s_SUBSYS) 538  AND  MI.AAL_SRC_SUB  NOT LIKE trim(RT.n_SUBSYS) 539  AND  MI.POST_DATE = RT.POST_DATE 540  ; WARNING: Variable POST_DATE already exists on file WORK.RULE_EXPLODE. NOTE: The SAS System stopped processing this step because of errors. NOTE: There were 10223 observations read from the data set WORK.MAP_INPUT. NOTE: There were 3998 observations read from the data set WORK.RULE_TRANS3.       WHERE POST_DATE='20150820'; WARNING: The data set WORK.RULE_EXPLODE may be incomplete.  When this step was stopped there were 21849 observations and 63 variables. WARNING: Data set WORK.RULE_EXPLODE was not replaced because this step was stopped. NOTE: PROCEDURE SQL used (Total process time):       real time           29.42 seconds       user cpu time       28.61 seconds       system cpu time     0.70 seconds       memory              1826231.12k       OS Memory           2097020.00k       Timestamp           27/08/2015 06:57:50 AM       Step Count                        34  Switch Count  3       Page Faults                       0       Page Reclaims                     471749       Page Swaps                        0       Voluntary Context Switches        26       Involuntary Context Switches      2977       Block Input Operations            37528       Block Output Operations           49936 541  quit; NOTE: Remote submit to GIDWSAS complete. OLD SERVER OUTPUT: 92   rsubmit; NOTE: Remote submit to GIDWSAS commencing. 69   OPTIONS FULLSTIMER MSGLEVEL=i; 70   proc sql; 71   create  table rule_explode as 72   SELECT  MI.*, RT.* 73   FROM MAP_INPUT AS MI LEFT JOIN rule_trans3 AS RT 74   ON   MI.AAL_SRC_SUB  NOT like (trim(RT.n_SUBSYS) ) 75   AND   MI.AAL_SRC_ACC like     (trim(RT.s_account) ) 76   AND  MI.AAL_SRC_ACC  NOT like (trim(RT.n_account) ) 77   AND  MI.AAL_SRC_CO   like    (trim(RT.s_COMPANY_CD) ) 78   and  MI.AAL_SRC_CO   not like (trim(RT.n_COMPANY_CD) ) 79   AND  MI.AAL_SRC_BR   like     (trim(RT.s_BRANCH) ) 80   and  MI.AAL_SRC_BR   not like (trim(RT.n_BRANCH) ) 81   AND  MI.AAL_SRC_PROD like    (trim(RT.s_PRODUCT) ) 82   AND  MI.AAL_SRC_PROD NOT like (trim(RT.n_PRODUCT) ) 83   AND  MI.POST_DATE = RT.POST_DATE 84   ; WARNING: Variable POST_DATE already exists on file WORK.RULE_EXPLODE. NOTE: SAS threaded sort was used. NOTE: Table WORK.RULE_EXPLODE created, with 51672 rows and 63 columns. 85   quit; NOTE: PROCEDURE SQL used (Total process time):       real time           1:28.90       user cpu time       1:28.04       system cpu time     0.61 seconds       Memory                            1308k       Page Faults                       0       Page Reclaims                     0       Page Swaps                        0       Voluntary Context Switches        3041       Involuntary Context Switches      2394       Block Input Operations            0       Block Output Operations           0 NOTE: Remote submit to GIDWSAS complete. ... View more

eq: cannot be used AND  MI.AAL_SRC_SUB  NOT  eq: trim(RT.n_SUBSYS)                                  -                                  22                                  200 ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, (, *, +, -, ALL, ANY, BTRIM, CALCULATED, CASE, INPUT, PUT, SELECT, SOME, SUBSTRING, TRANSLATE, USER. ERROR 200-322: The symbol is not recognized and will be ignored. ... View more

Contains gives 0 records, not around 50k as in the old server 123  RSUBMIT; NOTE: Remote submit to GIDWSAS commencing. 542  OPTIONS FULLSTIMER MSGLEVEL=i; 543  proc sql; 544  create  table rule_explode as 545  SELECT  MI.*, RT.* 546  FROM MAP_INPUT AS MI 547  INNER JOIN rule_trans3 AS RT 548  ON   MI.AAL_SRC_ACC  contains   trim(RT.s_account) 549  AND  MI.AAL_SRC_ACC  NOT CONTAINS trim(RT.n_account) 550  AND  MI.AAL_SRC_CO   CONTAINS     trim(RT.s_COMPANY_CD) 551  and  MI.AAL_SRC_CO   not CONTAINS trim(RT.n_COMPANY_CD) 552  AND  MI.AAL_SRC_BR   CONTAINS     trim(RT.s_BRANCH) 553  and  MI.AAL_SRC_BR   not CONTAINS trim(RT.n_BRANCH) 554  AND  MI.AAL_SRC_PROD CONTAINS     trim(RT.s_PRODUCT) 555  AND  MI.AAL_SRC_PROD NOT CONTAINS trim(RT.n_PRODUCT) 556  AND  MI.AAL_SRC_SUB  CONTAINS     trim(RT.s_SUBSYS) 557  AND  MI.AAL_SRC_SUB  NOT CONTAINS trim(RT.n_SUBSYS) 558  AND  MI.POST_DATE = RT.POST_DATE 559  ; WARNING: Variable POST_DATE already exists on file WORK.RULE_EXPLODE. NOTE: Table WORK.RULE_EXPLODE created, with 0 rows and 63 columns. 560  quit; NOTE: PROCEDURE SQL used (Total process time):       real time           13.38 seconds       user cpu time       13.32 seconds       system cpu time     0.01 seconds       memory              3533.90k       OS Memory           18364.00k       Timestamp           27/08/2015 07:02:24 AM       Step Count                        35  Switch Count  0       Page Faults                       0       Page Reclaims                     453       Page Swaps                        0       Voluntary Context Switches        5       Involuntary Context Switches      1370       Block Input Operations            3064       Block Output Operations           216 NOTE: Remote submit to GIDWSAS complete. ... View more

Below is the complete log one from the old sas server and one from the new Sas server, having the issue in the new server only: NEW SERVER LOG: NOTE: Remote submit to GIDWSAS commencing. 85   proc sql; 86   create  table rule_explode as 87   SELECT  MI.*, RT.* 88   FROM MAP_INPUT AS MI LEFT JOIN rule_trans3 AS RT 89   ON   MI.AAL_SRC_ACC  LIKE     (trim(RT.s_account) ) 90   AND  MI.AAL_SRC_ACC  NOT LIKE (trim(RT.n_account) ) 91   AND  MI.AAL_SRC_CO   LIKE     (trim(RT.s_COMPANY_CD) ) 92   and  MI.AAL_SRC_CO   not LIKE (trim(RT.n_COMPANY_CD) ) 93   AND  MI.AAL_SRC_BR   LIKE     (trim(RT.s_BRANCH) ) 94   and  MI.AAL_SRC_BR   not LIKE (trim(RT.n_BRANCH) ) 95   AND  MI.AAL_SRC_PROD LIKE     (trim(RT.s_PRODUCT) ) 96   AND  MI.AAL_SRC_PROD NOT LIKE (trim(RT.n_PRODUCT) ) 97   AND  MI.AAL_SRC_SUB  LIKE     (trim(RT.s_SUBSYS) ) 98   AND  MI.AAL_SRC_SUB  NOT LIKE (trim(RT.n_SUBSYS) ) 99   AND  MI.POST_DATE = RT.POST_DATE 100  ; WARNING: Variable POST_DATE already exists on file WORK.RULE_EXPLODE. NOTE: The SAS System stopped processing this step because of errors. NOTE: There were 10223 observations read from the data set WORK.MAP_INPUT. NOTE: There were 17880 observations read from the data set WORK.RULE_TRANS3. WARNING: The data set WORK.RULE_EXPLODE may be incomplete.  When this step was stopped there were 11685 observations and 63 variables. WARNING: Data set WORK.RULE_EXPLODE was not replaced because this step was stopped. NOTE: PROCEDURE SQL used (Total process time):       real time           32.78 seconds       cpu time            32.69 seconds 101  quit; NOTE: Remote submit to GIDWSAS complete. OLD SERVER LOG: NOTE: Remote submit to GIDWSAS commencing. 38   proc sql; 39   create  table rule_explode as 40   SELECT  MI.*, RT.* 41   FROM MAP_INPUT AS MI LEFT JOIN rule_trans3 AS RT 42   ON   MI.AAL_SRC_SUB  NOT like (trim(RT.n_SUBSYS) ) 43   AND   MI.AAL_SRC_ACC like     (trim(RT.s_account) ) 44   AND  MI.AAL_SRC_ACC  NOT like (trim(RT.n_account) ) 45   AND  MI.AAL_SRC_CO   like    (trim(RT.s_COMPANY_CD) ) 46   and  MI.AAL_SRC_CO   not like (trim(RT.n_COMPANY_CD) ) 47   AND  MI.AAL_SRC_BR   like     (trim(RT.s_BRANCH) ) 48   and  MI.AAL_SRC_BR   not like (trim(RT.n_BRANCH) ) 49   /*AND  MI.AAL_SRC_PROD like    (trim(RT.s_PRODUCT) )*/ 50   AND  MI.AAL_SRC_PROD like    (trim(RT.s_PRODUCT) ) 51   AND  MI.AAL_SRC_PROD NOT like (trim(RT.n_PRODUCT) ) 52   AND  MI.POST_DATE = RT.POST_DATE 53   ; WARNING: Variable POST_DATE already exists on file WORK.RULE_EXPLODE. NOTE: Table WORK.RULE_EXPLODE created, with 51672 rows and 63 columns. 54   quit; NOTE: PROCEDURE SQL used (Total process time):       real time           1:29.94       cpu time            1:29.83 ... View more

It does, but the problem is when it does startdt to enddt where enddt bcomes 9999-01-01 it creates huge temp dataset which makes the solution unnecessarily data intensive. Trying to find a way to resolve the issue. ... View more

Hi Keshan, 99 is actually 9999, I've updated the question. Your answer looks very valid, will check more and update. Thanks a lot. ... View more

Unable to change the dates, since it will affect the validity of other ids, I need to find out valid ids on a given date by creating time lines as below id 1 26-Feb-15 12-Apr-15 1 13-Apr-15 23-Apr-15 1 24-Apr-15 26-Apr-15 2 26-Feb-15 12-Apr-15 2 13-Apr-15 23-Apr-15 2 24-Apr-15 26-Apr-15 2 27-Apr-15 30-Apr-15 3 13-Apr-15 23-Apr-15 4 24-Apr-15 26-Apr-15 4 27-Apr-15 30-Apr-15 4 1-May-15 1-Jan-99 5 24-Apr-15 26-Apr-15 5 27-Apr-15 30-Apr-15 1-May-15 1-Jan-99 5 validity ids 1,2 are valid ids 1,2,3 are valid ids 1,2,3,4 are valid ids 2,4,5 are valid ids 4, 5 are vaid ... View more

Hi, Actually any date range can overlap another, I just update the question to specify the point this is sample data for one group A. once dates are aligned for group A there are only sequently aligned start times and end times without overlapping each other. in your solution when we do startdt to enddt , for observations where enddt=01-Jan-9999 gives extremely large number of observations to work out. ... View more