1 The SAS System 07:24 Wednesday, September 21, 2016 1 ;*';*";*/;quit;run; 2 OPTIONS PAGENO=MIN; 3 %LET _CLIENTTASKLABEL='SP_CALL'; 4 %LET _CLIENTPROJECTPATH='D:\Entwicklung\SAS\Enterprise Guide\SandBox\SandBox2.egp'; 5 %LET _CLIENTPROJECTNAME='SandBox2.egp'; 6 %LET _SASPROGRAMFILE=; 7 %LET LOCATION = 1; 8 %LET LOCATION2 = 3; 9 %LET LOCATION0 = 2; 10 %LET LOCATION1 = 1; 11 %LET LOCATION_count = 2; 12 13 ODS _ALL_ CLOSE; 14 OPTIONS DEV=ACTIVEX; 15 GOPTIONS XPIXELS=0 YPIXELS=0; 16 FILENAME EGSR TEMP; 17 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR 18 STYLE=HtmlBlue 19 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css") 20 NOGTITLE 21 NOGFOOTNOTE 22 GPATH=&sasworklocation 23 ENCODING=UTF8 24 options(rolap="on") 25 ; NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR 26 27 GOPTIONS ACCESSIBLE; 28 PROC STP PROGRAM="/User Folders/bm120869/My Folder/SandBox" ; 29 30 INPUTPARAM LOCATION="1"; 31 32 33 RUN; NOTE: The stored process will execute locally. NOTE: PROC_STP: ====== Proc STP Execution Starting ====== NOTE: PROC_STP: ====== Stored Process: /User Folders/bm120869/My Folder/SandBox ====== The SAS System >>> SAS Macro Variables: LOCATION=1 _CLIENT=PROCSTP TKESTP Windows X64_DS08R2 X86_64 6.2 _METAPERSON=bm120869 _METAUSER=bm120869@!*(generatedpassworddomain)*! _RESULT=STATUS 2 * Begin EG generated code (do not edit this line); 3 * 4 * Stored process registered by 5 * Enterprise Guide Stored Process Manager V7.1 6 * 7 * ==================================================================== 8 * Stored process name: SandBox 9 * ==================================================================== 10 * 11 * Stored process prompt dictionary: 12 * ____________________________________ 13 * LOCATION 14 * Type: Numeric 15 * Label: LOCATION 16 * Attr: Visible 17 * ____________________________________ 18 *; 19 20 21 *ProcessBody; 22 23 %global LOCATION; 24 25 %STPBEGIN; 26 27 * End EG generated code (do not edit this line); 28 29 The SAS System 30 /* --- Start of code for "SP_BODY". --- */ 31 %macro _eg_WhereParam( COLUMN, PARM, OPERATOR, TYPE=S, MATCHALL=_ALL_VALUES_, MATCHALL_CLAUSE=1, MAX= , IS_EXPLICIT=0); 32 33 %local q1 q2 sq1 sq2; 34 %local isEmpty; 35 %local isEqual isNotEqual; 36 %local isIn isNotIn; 37 %local isString; 38 %local isBetween; 39 40 %let isEqual = ("%QUPCASE(&OPERATOR)" = "EQ" OR "&OPERATOR" = "="); 41 %let isNotEqual = ("%QUPCASE(&OPERATOR)" = "NE" OR "&OPERATOR" = "<>"); 42 %let isIn = ("%QUPCASE(&OPERATOR)" = "IN"); 43 %let isNotIn = ("%QUPCASE(&OPERATOR)" = "NOT IN"); 44 %let isString = (%QUPCASE(&TYPE) eq S or %QUPCASE(&TYPE) eq STRING ); 45 %PUT &=ISIN; 46 %if &isString %then 47 %do; 48 %let q1=%str(%"); 49 %let q2=%str(%"); 50 %let sq1=%str(%'); 51 %let sq2=%str(%'); 52 %end; 53 %else %if %QUPCASE(&TYPE) eq D or %QUPCASE(&TYPE) eq DATE %then 54 %do; 55 %let q1=%str(%"); 56 %let q2=%str(%"d); 57 %let sq1=%str(%'); 58 %let sq2=%str(%'); 59 %end; 60 %else %if %QUPCASE(&TYPE) eq T or %QUPCASE(&TYPE) eq TIME %then 61 %do; 62 %let q1=%str(%"); 63 %let q2=%str(%"t); 64 %let sq1=%str(%'); 65 %let sq2=%str(%'); 66 %end; 67 %else %if %QUPCASE(&TYPE) eq DT or %QUPCASE(&TYPE) eq DATETIME %then The SAS System 68 %do; 69 %let q1=%str(%"); 70 %let q2=%str(%"dt); 71 %let sq1=%str(%'); 72 %let sq2=%str(%'); 73 %end; 74 %else 75 %do; 76 %let q1=; 77 %let q2=; 78 %let sq1=; 79 %let sq2=; 80 %end; 81 82 %if "&PARM" = "" %then %let PARM=&COLUMN; 83 84 %let isBetween = ("%QUPCASE(&OPERATOR)"="BETWEEN" or "%QUPCASE(&OPERATOR)"="NOT BETWEEN"); 85 86 %if "&MAX" = "" %then %do; 87 %let MAX = &parm._MAX; 88 %if &isBetween %then %let PARM = &parm._MIN; 89 %end; 90 91 %if not %symexist(&PARM) or (&isBetween and not %symexist(&MAX)) %then %do; 92 %if &IS_EXPLICIT=0 %then %do; 93 not &MATCHALL_CLAUSE 94 %end; 95 %else %do; 96 not 1=1 97 %end; 98 %end; 99 %else %if "%qupcase(&&&PARM)" = "%qupcase(&MATCHALL)" %then %do; 100 %if &IS_EXPLICIT=0 %then %do; 101 &MATCHALL_CLAUSE 102 %end; 103 %else %do; 104 1=1 105 %end; The SAS System 106 %end; 107 %else %if (not %symexist(&PARM._count)) or &isBetween %then %do; 108 %let isEmpty = ("&&&PARM" = ""); 109 %if (&isEqual AND &isEmpty AND &isString) %then 110 &COLUMN is null; 111 %else %if (&isNotEqual AND &isEmpty AND &isString) %then 112 &COLUMN is not null; 113 %else %do; 114 %if &IS_EXPLICIT=0 %then %do; 115 &COLUMN &OPERATOR %unquote(&q1)&&&PARM%unquote(&q2) 116 %end; 117 %else %do; 118 &COLUMN &OPERATOR %unquote(%nrstr(&sq1))&&&PARM%unquote(%nrstr(&sq2)) 119 %end; 120 %if &isBetween %then 121 AND %unquote(&q1)&&&MAX%unquote(&q2); 122 %end; 123 %end; 124 %else 125 %do; 126 %local emptyList; 127 %let emptyList = %symexist(&PARM._count); 128 %if &emptyList %then %let emptyList = &&&PARM._count = 0; 129 %if (&emptyList) %then 130 %do; 131 %if (&isNotin) %then 132 1; 133 %else 134 0; 135 %end; 136 %else %if (&&&PARM._count = 1) %then 137 %do; 138 %let isEmpty = ("&&&PARM" = ""); 139 %if (&isIn AND &isEmpty AND &isString) %then 140 &COLUMN is null; 141 %else %if (&isNotin AND &isEmpty AND &isString) %then 142 &COLUMN is not null; 143 %else %do; The SAS System 144 %if &IS_EXPLICIT=0 %then %do; 145 &COLUMN &OPERATOR (%unquote(&q1)&&&PARM%unquote(&q2)) 146 %end; 147 %else %do; 148 &COLUMN &OPERATOR (%unquote(%nrstr(&sq1))&&&PARM%unquote(%nrstr(&sq2))) 149 %end; 150 %end; 151 %end; 152 %else 153 %do; 154 %local addIsNull addIsNotNull addComma; 155 %let addIsNull = %eval(0); 156 %let addIsNotNull = %eval(0); 157 %let addComma = %eval(0); 158 (&COLUMN &OPERATOR ( 159 %do i=1 %to &&&PARM._count; 160 %let isEmpty = ("&&&PARM&i" = ""); 161 %if (&isString AND &isEmpty AND (&isIn OR &isNotIn)) %then 162 %do; 163 %if (&isIn) %then %let addIsNull = 1; 164 %else %let addIsNotNull = 1; 165 %end; 166 %else 167 %do; 168 %if &addComma %then %do;,%end; 169 %if &IS_EXPLICIT=0 %then %do; 170 %unquote(&q1)&&&PARM&i%unquote(&q2) 171 %end; 172 %else %do; 173 %unquote(%nrstr(&sq1))&&&PARM&i%unquote(%nrstr(&sq2)) 174 %end; 175 %let addComma = %eval(1); 176 %end; 177 %end;) 178 %if &addIsNull %then OR &COLUMN is null; 179 %else %if &addIsNotNull %then AND &COLUMN is not null; 180 %do;) 181 %end; The SAS System 182 %end; 183 %end; 184 %mend _eg_WhereParam; 185 186 187 OPTIONS MPRINT; 188 189 190 DATA WORK.INPUT; 191 INFILE datalines; 192 INPUT location_id location_name : $11.; 193 datalines; NOTE: The data set WORK.INPUT has 5 observations and 2 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 199 ;; 200 RUN; 201 202 203 PROC SQL NOPRINT; 204 205 CREATE TABLE WORK.TEST AS 206 SELECT * FROM WORK.INPUT WHERE 207 %_eg_WhereParam(location_id, location, IN, TYPE=N ) ISIN=("IN" = "IN") MPRINT(_EG_WHEREPARAM): location_id IN 1 NOTE 139-205: Line generated by the macro function "UNQUOTE". 207 1 - 22 ERROR 22-322: Syntax error, expecting one of the following: (, SELECT. NOTE: Line generated by the macro function "UNQUOTE". 207 1 - 76 The SAS System ERROR 76-322: Syntax error, statement will be ignored. 208 ; NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. 209 RUN; 210 211 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 212 PROC REPORT DATA=WORK.TEST; ERROR: File WORK.TEST.DATA does not exist. 213 RUN; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE REPORT used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 214 215 /* --- End of code for "SP_BODY". --- */ 216 217 * Begin EG generated code (do not edit this line); 218 ;*';*";*/;quit; 219 %STPEND; MPRINT(STPEND): options nonotes; MPRINT(STPEND): ods _ALL_ close; MPRINT(STPEND): options NOTES; 220 221 * End EG generated code (do not edit this line); NOTE: PROC_STP: ====== Stored Process: /User Folders/bm120869/My Folder/SandBox Return Status = 2000 ====== NOTE: PROC_STP: ====== Proc STP Execution Ending ====== NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE STP used (Total process time): real time 0.17 seconds cpu time 0.14 seconds 34 35 GOPTIONS NOACCESSIBLE; 36 %LET _CLIENTTASKLABEL=; 37 %LET _CLIENTPROJECTPATH=; 38 %LET _CLIENTPROJECTNAME=; 39 %LET _SASPROGRAMFILE=; 40 %SYMDEL LOCATION; 41 %SYMDEL LOCATION2; 42 %SYMDEL LOCATION1; 43 %SYMDEL LOCATION0; 44 %SYMDEL LOCATION_count; 2 The SAS System 07:24 Wednesday, September 21, 2016 45 46 ;*';*";*/;quit;run; 47 ODS _ALL_ CLOSE; 48 49 50 QUIT; RUN; 51