NOTE: PROCEDURE PRINTTO used (Total process time): real time 0.01 seconds cpu time 0.00 seconds 571 572 options mlogic SYMBOLGEN; 573 574 %macro In_list (var=,vals=); /* check */ 575 proc sql; create table values_found as select distinct &VAR from in.test group by 1; 575! run; 576 proc sql; create table &vals as select distinct variable_&vals from 576! DC.input_analysis_res where variable_name='&VAR' ;run; 577 proc sql; create table out_of_&vals as select distinct a.*, b.* from values_found a, 577! &vals b ;run; 578 proc sql; create table out_of_&vals as select &VAR from out_of_&vals where 578! Variable_&vals not contains &VAR ;run; 579 proc sql; create table out_of_&vals as select b.&VAR, count(*) as _n_ from out_of_&vals 579! a, in.test b 580 where a.&VAR=b.&VAR group by 1 ;run; 581 proc sql; create table number_unexpected_values as select sum(_n_) as 581! number_unexpected_values from out_of_&vals;run; 582 data unexpected_values_list (drop=&VAR rename=(tmp=&VAR)); 583 length tmp $2000; set out_of_&vals end=last; retain tmp; tmp=catx(",",tmp,&VAR); 583! if last then output ;run; 584 proc sql; update DC.input_analysis_res 585 set number_unexpected_value = (select number_unexpected_values FROM 585! number_unexpected_values) WHERE Variable_Name_Original='&VAR' ;run; 586 proc sql; update DC.input_analysis_res 587 set Unexpected_values = (select &VAR FROM unexpected_values_list) WHERE 587! Variable_Name_Original='&VAR' ;run; 588 %mend In_List; 589 590 591 /* check 02: count outliers 592 proc sql; create table upper_bound as select Variable_max from DC.input_analysis_res where 592! variable_name='ALTERNATIVEBEGIN'; 593 proc sql; create table lower_bound as select Variable_min from DC.input_analysis_res where 593! variable_name='ALTERNATIVEBEGIN'; 594 proc sql; create table base as select distinct ALTERNATIVEBEGIN from in.test ;run; 595 proc sql; create table base as select a.*, b.*, c.* from base a, upper_bound b, lower_bound 595! c ;run; 596 proc sql; create table num_ourliers as select 597 case when ALTERNATIVEBEGIN>Variable_max then 1 else 0 end as n_over, 598 case when ALTERNATIVEBEGIN, =, >, >=, ?, AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, LEN, LENGTH, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=. NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.00 seconds NOTE: Line generated by the CALL EXECUTE routine. 2 + create table out_of_DE FR JP as select distinct a.*, b.* from values_found a, DE FR JP b -- -- 22 78 202 76 2 !+;run; proc sql; create table out_of_DE FR JP as select VPS_AUFENTHALTSLAND from ERROR 22-322: Syntax error, expecting one of the following: (, '.', AS, LIKE. ERROR 78-322: Expecting a ','. ERROR 202-322: The option or parameter is not recognized and will be ignored. ERROR 76-322: Syntax error, statement will be ignored. NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: Line generated by the CALL EXECUTE routine. 2 + create table out_of_DE FR JP as select VPS_AUFENTHALTSLAND from out_of_DE FR JP where -- -- -- 22 78 76 202 ERROR 22-322: Syntax error, expecting one of the following: (, '.', AS, LIKE. ERROR 78-322: Expecting a ','. ERROR 76-322: Syntax error, statement will be ignored. ERROR 202-322: The option or parameter is not recognized and will be ignored. 2 !+ create table out_of_DE FR JP as select VPS_AUFENTHALTSLAND from out_of_DE FR JP where 2 !+Variable_DE FR JP not contains VPS_AUFENTHALTSLAND ;run; -- 22 ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, (, *, **, +, -, '.', /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, EXCEPT, GE, GET, GROUP, GT, GTT, HAVING, IN, INTERSECT, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ORDER, OUTER, UNION, ^, ^=, |, ||, ~, ~=. NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 3 + proc sql; NOTE: Line generated by the CALL EXECUTE routine. 3 + create table out_of_DE FR JP as select b.VPS_AUFENTHALTSLAND, count(*) as _n_ from -- -- 22 78 202 3 !+out_of_DE FR JP a, in.test b where a.VPS_AUFENTHALTSLAND=b.VPS_AUFENTHALTSLAND group by 3 !+ 1 ;run; proc sql; create table number_unexpected_values as ERROR 22-322: Syntax error, expecting one of the following: (, '.', AS, LIKE. ERROR 78-322: Expecting a ','. ERROR 202-322: The option or parameter is not recognized and will be ignored. NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: Line generated by the CALL EXECUTE routine. 4 + select sum(_n_) as number_unexpected_values from out_of_DE FR JP; -- 22 76 ERROR 22-322: Syntax error, expecting one of the following: ',', GROUP, HAVING, ORDER, WHERE. ERROR 76-322: Syntax error, statement will be ignored. NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds ERROR: File WORK.OUT_OF_DE.DATA does not exist. ERROR: File WORK.FR.DATA does not exist. ERROR: File WORK.JP.DATA does not exist. 5 +(",",tmp,VPS_AUFENTHALTSLAND); if last then output ;run; NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.UNEXPECTED_VALUES_LIST may be incomplete. When this step was stopped there were 0 observations and 1 variables. WARNING: Data set WORK.UNEXPECTED_VALUES_LIST was not replaced because this step was stopped. NOTE: DATA statement used (Total process time): real time 0.06 seconds cpu time 0.01 seconds 5 + proc sql; 5 + update DC.input_analysis_res set number_unexpected_value = (select number_unexpected_values FROM number_unexpected_values) WHERE Variable_Name_Original='&VAR' ; ERROR: File WORK.NUMBER_UNEXPECTED_VALUES.DATA does not exist. 5 + run; NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.00 seconds 5 + proc sql; 6 + update DC.input_analysis_res set Unexpected_values = (select VPS_AUFENTHALTSLAND FROM unexpected_values_list) WHERE Variable_Name_Original='&VAR' ; WARNING: Character expression will be truncated when assigned to character column Unexpected_values. NOTE: No rows were updated in DC.INPUT_ANALYSIS_RES. 6 + run;; NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. NOTE: PROCEDURE SQL used (Total process time): real time 0.07 seconds cpu time 0.01 seconds 7 + proc sql; 7 + create table values_found as select distinct VPS_AUFENTHALTSLAND from in.test group by 1; WARNING: A GROUP BY clause has been transformed into an ORDER BY clause because neither the SELECT clause nor the optional HAVING clause of the associated table-expression referenced a summary function. NOTE: Table WORK.VALUES_FOUND created, with 219 rows and 1 columns. 7 + run; NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. NOTE: PROCEDURE SQL used (Total process time): real time 0.70 seconds cpu time 0.63 seconds 7 + proc sql; NOTE: Line generated by the CALL EXECUTE routine. 7 + create table DE FR JP as select distinct variable_DE FR JP from DC.input_analysis_res -- -- 22 202 202 ERROR 22-322: Syntax error, expecting one of the following: (, '.', AS, LIKE. ERROR 202-322: The option or parameter is not recognized and will be ignored. 7 !+ create table DE FR JP as select distinct variable_DE FR JP from DC.input_analysis_res -- 22 7 !+where variable_name='&VAR' ;run; proc sql; ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, (, *, **, +, ',', -, '.', /, <, <=, <>, =, >, >=, ?, AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, LEN, LENGTH, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=. NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.00 seconds NOTE: Line generated by the CALL EXECUTE routine. 8 + create table out_of_DE FR JP as select distinct a.*, b.* from values_found a, DE FR JP b -- -- 22 78 202 76 8 !+;run; proc sql; create table out_of_DE FR JP as select VPS_AUFENTHALTSLAND from ERROR 22-322: Syntax error, expecting one of the following: (, '.', AS, LIKE. ERROR 78-322: Expecting a ','. ERROR 202-322: The option or parameter is not recognized and will be ignored. ERROR 76-322: Syntax error, statement will be ignored. NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: Line generated by the CALL EXECUTE routine. 8 + create table out_of_DE FR JP as select VPS_AUFENTHALTSLAND from out_of_DE FR JP where -- -- -- 22 78 76 202 ERROR 22-322: Syntax error, expecting one of the following: (, '.', AS, LIKE. ERROR 78-322: Expecting a ','. ERROR 76-322: Syntax error, statement will be ignored. ERROR 202-322: The option or parameter is not recognized and will be ignored. 8 !+ create table out_of_DE FR JP as select VPS_AUFENTHALTSLAND from out_of_DE FR JP where 8 !+Variable_DE FR JP not contains VPS_AUFENTHALTSLAND ;run; -- 22 ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, (, *, **, +, -, '.', /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, EXCEPT, GE, GET, GROUP, GT, GTT, HAVING, IN, INTERSECT, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ORDER, OUTER, UNION, ^, ^=, |, ||, ~, ~=. NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 9 + proc sql; NOTE: Line generated by the CALL EXECUTE routine. 9 + create table out_of_DE FR JP as select b.VPS_AUFENTHALTSLAND, count(*) as _n_ from -- -- 22 78 202 9 !+out_of_DE FR JP a, in.test b where a.VPS_AUFENTHALTSLAND=b.VPS_AUFENTHALTSLAND group by 9 !+ 1 ;run; proc sql; create table number_unexpected_values as ERROR 22-322: Syntax error, expecting one of the following: (, '.', AS, LIKE. ERROR 78-322: Expecting a ','. ERROR 202-322: The option or parameter is not recognized and will be ignored. NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: Line generated by the CALL EXECUTE routine. 10 + select sum(_n_) as number_unexpected_values from out_of_DE FR JP; -- 22 76 ERROR 22-322: Syntax error, expecting one of the following: ',', GROUP, HAVING, ORDER, WHERE. ERROR 76-322: Syntax error, statement will be ignored. NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.01 seconds ERROR: File WORK.OUT_OF_DE.DATA does not exist. ERROR: File WORK.FR.DATA does not exist. ERROR: File WORK.JP.DATA does not exist. 11 +(",",tmp,VPS_AUFENTHALTSLAND); if last then output ;run; NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.UNEXPECTED_VALUES_LIST may be incomplete. When this step was stopped there were 0 observations and 1 variables. WARNING: Data set WORK.UNEXPECTED_VALUES_LIST was not replaced because this step was stopped. NOTE: DATA statement used (Total process time): real time 0.04 seconds cpu time 0.00 seconds 11 + proc sql; 11 + update DC.input_analysis_res set number_unexpected_value = (select number_unexpected_values FROM number_unexpected_values) WHERE Variable_Name_Original='&VAR' ; ERROR: File WORK.NUMBER_UNEXPECTED_VALUES.DATA does not exist. 11 + run; NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.00 seconds