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
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;
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.
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
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;
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)
Nope, I just meant there was a typo, it should read CONTAINS, not CONATAINS.
Yeah. It is typo. The code is updated.
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.
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.
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;
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.
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
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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: