String Comparision with Like Or use of a SAS Function

[ven]

Hi ALL,

I am having a great difficulty in creating a SAS 9.4 dataset by using below query, It is not created fully but gives the error below(Same query gives the required output 50k rows in DB2 database),

Can someone let me know how can I use a SAS function or different technique to achive the same results.

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.

NOTE: PROCEDURE SQL used (Total process time):

SAS PROC SQL USED:

PROC SQL;

CREATE  TABLE RULE_EXPLODE AS

SELECT  MI.*, RT.*

FROM MAP_INPUT AS MI LEFT JOIN RULE_TRANS3 AS RT

ON   MI.AAL_SRC_SUB  NOT LIKE (TRIM(RT.N_SUBSYS) )

AND   MI.AAL_SRC_ACC LIKE     (TRIM(RT.S_ACCOUNT) )

AND  MI.AAL_SRC_ACC  NOT LIKE (TRIM(RT.N_ACCOUNT) )

AND  MI.AAL_SRC_CO   LIKE    (TRIM(RT.S_COMPANY_CD) )

AND  MI.AAL_SRC_CO   NOT LIKE (TRIM(RT.N_COMPANY_CD) )

AND  MI.AAL_SRC_BR   LIKE     (TRIM(RT.S_BRANCH) )

AND  MI.AAL_SRC_BR   NOT LIKE (TRIM(RT.N_BRANCH) )

AND  MI.AAL_SRC_PROD LIKE    (TRIM(RT.S_PRODUCT) )

AND  MI.AAL_SRC_PROD NOT LIKE (TRIM(RT.N_PRODUCT) )

AND  MI.POST_DATE = RT.POST_DATE

;

QUIT;

Thanks in advance

[LinusH]

Seems you missed to put the actual error in the post?

Since the SQL is creating some output I conclude that it's not anything wrong with the syntax.

Memory issue?

try OPTIONS FULLSTIMER MSGLEVEL=i;

to get information about the resources used.

Be sure that your saswork have the necessary space available.

[Ksharp]

Change your operator like this :

ON   MI.AAL_SRC_SUB  NOT CONTAINS TRIM(RT.N_SUBSYS)

AND   MI.AAL_SRC_ACC CONTAINS TRIM(RT.S_ACCOUNT) )

Message was edited by: xia keshan

[RW9]

Hi Xia,  do you mean CONTAINS?

For OP, what is the error message you get, post more of the log.  Also you do not need all those trims() and likes and not likes in there, simple equals or not equals will achieve the same thing:

proc sql;

  create  table RULE_EXPLODE as

  select  MI.*,

          RT.*

  from    MAP_INPUT as MI

  left join RULE_TRANS3 as RT

  on      MI.AAL_SRC_SUB ne RT.N_SUBSYS

    and   MI.AAL_SRC_ACC=RT.S_ACCOUNT

    and   MI.AAL_SRC_ACC ne RT.N_ACCOUNT

    and   MI.AAL_SRC_CO=RT.S_COMPANY_CD

    and   MI.AAL_SRC_CO ne RT.N_COMPANY_CD

    and   MI.AAL_SRC_BR=RT.S_BRANCH

    and   MI.AAL_SRC_BR ne RT.N_BRANCH

    and   MI.AAL_SRC_PROD=RT.S_PRODUCT

    and   MI.AAL_SRC_PROD ne RT.N_PRODUCT

    and   MI.POST_DATE=RT.POST_DATE;

quit;

[Ksharp]

Not Sure. Just Guess.

Maybe should be another operator:

on      MI.AAL_SRC_SUB not eq:  trim(RT.N_SUBSYS)

    and   MI.AAL_SRC_ACC eq: trim(RT.S_ACCOUNT)

[RW9]

Nope, I just meant there was a typo, it should read CONTAINS, not CONATAINS.

[Ksharp]

Yeah. It is typo. The code is updated.

[ven]

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.

[Ksharp]

Sorry. It is EQT for sql, eq: for data step.

I am doubted that what value is RT.s_account .....  . Curious LIKE can do some work.

[ven · Accepted solution]

Most of the s_account and like values got only the value "%" , causing a cartison product. I used below to fix the the issue. hope it s the most efficent way found so far:

RSUBMIT;
proc sql;
create  table rule_trans3_1 as
SELECT  RT.*,
CASE WHEN trim(RT.s_account) NOT = '%' THEN trim(RT.s_account)
  ELSE 'NULL' END  as s_account_1,

CASE WHEN trim(RT.s_COMPANY_CD) NOT = '%' THEN trim(RT.s_COMPANY_CD)
  ELSE 'NULL' END  as s_COMPANY_CD_1,

CASE WHEN trim(RT.s_BRANCH) NOT = '%' THEN trim(RT.s_BRANCH)
  ELSE 'NULL' END  as s_BRANCH_1,

CASE WHEN trim(RT.s_PRODUCT) NOT = '%' THEN trim(RT.s_PRODUCT)
  ELSE 'NULL' END  as s_PRODUCT_1,

CASE WHEN trim(RT.s_SUBSYS) NOT = '%' THEN trim(RT.s_SUBSYS)
  ELSE 'NULL' END  as s_SUBSYS_1

FROM rule_trans3 AS RT
;
quit;
endrsubmit;

RSUBMIT;
proc sql;
create  table rule_explode_new as
SELECT  MI.*, RT.*
FROM MAP_INPUT AS MI LEFT OUTER JOIN rule_trans3_1 AS RT
ON   MI.AAL_SRC_ACC  NOT LIKE (trim(RT.n_account) )
AND (MI.AAL_SRC_ACC LIKE trim(RT.s_account_1) OR RT.s_account_1 = 'NULL')
AND ( MI.AAL_SRC_CO LIKE trim(RT.s_COMPANY_CD_1) OR RT.s_COMPANY_CD_1 = 'NULL')
and  MI.AAL_SRC_CO   not LIKE (trim(RT.n_COMPANY_CD) )
AND ( MI.AAL_SRC_BR LIKE trim(RT.s_BRANCH_1) OR RT.s_BRANCH_1 = 'NULL')
and  MI.AAL_SRC_BR   not LIKE (trim(RT.n_BRANCH) )
AND ( MI.AAL_SRC_PROD LIKE trim(RT.s_PRODUCT_1) OR RT.s_PRODUCT_1 = 'NULL')
AND  MI.AAL_SRC_PROD NOT LIKE (trim(RT.n_PRODUCT) )
AND  ( MI.AAL_SRC_SUB LIKE trim(RT.s_SUBSYS_1) OR RT.s_SUBSYS_1 = 'NULL')
AND  MI.AAL_SRC_SUB  NOT LIKE (trim(RT.n_SUBSYS) )
AND  MI.POST_DATE = RT.POST_DATE
;
quit;
endrsubmit;

RSUBMIT;
Data rule_explode (drop =s_account_1 s_COMPANY_CD_1 s_BRANCH_1 s_PRODUCT_1 s_SUBSYS_1);
set rule_explode_new;
run;
endrsubmit;

[ven]

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.

[ven]

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

[ven]

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.

[Tom]

Looks like you just ran out of space to write the dataset.  Either allocate more space to the disk that WORK is using or write the results to another disk using a libref other than WORK.

[ven]

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.