topic Re: String Comparision with Like Or use of a SAS Function in SAS Programming

String Comparision with Like Or use of a SAS Function

ven — Wed, 26 Aug 2015 07:01:17 GMT

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

Re: String Comparision with Like Or use of a SAS Function

LinusH — Wed, 26 Aug 2015 07:41:41 GMT

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.

Re: String Comparision with Like Or use of a SAS Function

Ksharp — Wed, 26 Aug 2015 08:53:14 GMT

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

Re: String Comparision with Like Or use of a SAS Function

RW9 — Wed, 26 Aug 2015 09:45:25 GMT

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;

Re: String Comparision with Like Or use of a SAS Function

Ksharp — Wed, 26 Aug 2015 11:40:39 GMT

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)

Re: String Comparision with Like Or use of a SAS Function

RW9 — Wed, 26 Aug 2015 11:56:36 GMT

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

Re: String Comparision with Like Or use of a SAS Function

Ksharp — Wed, 26 Aug 2015 12:01:21 GMT

Yeah. It is typo. The code is updated.

Re: String Comparision with Like Or use of a SAS Function

ven — Wed, 26 Aug 2015 20:44:44 GMT

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

Re: String Comparision with Like Or use of a SAS Function

ven — Wed, 26 Aug 2015 21:04:08 GMT

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.

Re: String Comparision with Like Or use of a SAS Function

ven — Wed, 26 Aug 2015 21:07:49 GMT

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.

Re: String Comparision with Like Or use of a SAS Function

ven — Wed, 26 Aug 2015 21:35:38 GMT

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.

Re: String Comparision with Like Or use of a SAS Function

Tom — Wed, 26 Aug 2015 21:47:55 GMT

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.

Re: String Comparision with Like Or use of a SAS Function

ven — Wed, 26 Aug 2015 22:28:28 GMT

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.

Re: String Comparision with Like Or use of a SAS Function

SASKiwi — Wed, 26 Aug 2015 23:19:51 GMT

I would simplify your query down to something that works then progressively add lines back until it fails. Try starting with something like this:

proc sql;

SELECT count(*) as count

FROM MAP_INPUT AS MI

INNER JOIN rule_trans3 AS RT

ON MI.POST_DATE = RT.POST_DATE

;

Re: String Comparision with Like Or use of a SAS Function

ven — Thu, 27 Aug 2015 00:10:29 GMT

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

Re: String Comparision with Like Or use of a SAS Function

SASKiwi — Thu, 27 Aug 2015 05:33:05 GMT

Please explain why you need to use LIKE.

Re: String Comparision with Like Or use of a SAS Function

Ksharp — Thu, 27 Aug 2015 07:02:44 GMT

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.

Re: String Comparision with Like Or use of a SAS Function

ven — Thu, 27 Aug 2015 07:14:55 GMT

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;