1 The SAS System 12:05 Monday, February 5, 2018 NOTE: Copyright (c) 2002-2010 by SAS Institute Inc., Cary, NC, USA. NOTE: SAS (r) Proprietary Software 9.3 (TS1M2) Licensed to BANCO POPULAR DOMINICANO, S.A- BANCO MULTIPLE, Site 70085111. NOTE: This session is executing on the AIX 7.1 (AIX 64) platform. NOTE: Enhanced analytical products: SAS/STAT 12.1, SAS/ETS 12.1, SAS/OR 12.2, SAS/QC 12.1 You are running SAS 9. Some SAS 8 files will be automatically converted by the V9 engine; others are incompatible. Please see http://support.sas.com/rnd/migration/planning/platform/64bit.html PROC MIGRATE will preserve current SAS file attributes and is recommended for converting all your SAS libraries from any SAS 8 release to SAS 9. For details and examples, please see http://support.sas.com/rnd/migration/index.html This message is contained in the SAS news file, and is presented upon initialization. Edit the file "news" in the "misc/base" directory to display site-specific news and information in the program log. The command line option "-nonews" will prevent this display. NOTE: SAS Initialization used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: The autoexec file, /sas/sasconfig/Lev1/SASApp/WorkspaceServer/autoexec.sas, was executed at server initialization. >>> SAS Macro Variables: CPSLDDQ="co05sascps" DBCASH=LAB01VMGSQL2014_CASH FECHA_CONSULTA_MAX=20Dec2017 FECHA_CONSULTA_MAX_LABEL=December 20, 2017 FECHA_CONSULTA_MIN=01Dec2017 FECHA_CONSULTA_MIN_LABEL=December 01, 2017 LIBDDS=LIBDDS47 LIBIDEA=LIBLIDEA MAXDATEDDS= 02FEB2018:00:00:00 MAXIMA_FECHA= NRO_TARJETA=0005415990585016454 ORA_DEFDIR=BL_DEFAULT_DIR='/sasworkp/' ORA_SQLLDR='/oracle/app/product/11.2.0/client64_1/bin/sqlldr' PATHDDS=SDDDBPRO PATHIDEA=DMADBPRO PATHLDDQ=LandingData PWDCASH={SAS002}ECAA39442B8892AE205EE515 PWDDDS="{SAS002}4790305741A718E140F668D24586352716C73D39" PWDIDEA="{SAS002}341F943247DF252F16DD6A9F4098ABFE" PWDLDDQ="{sas002}0F833D492E8F3CC33D7F97024730638628D53CD9" SCDUP_LOADTIME=05FEB2018:12:05 2 The SAS System 12:05 Monday, February 5, 2018 SCHEMADDS=SASDDS SCHEMAIDEA=DMIDEAPRO SEARCH_TO_DTTM=TO_DATE('01JAN5999:00:00:00','DDMonYYYY:HH24:MI:SS') SYSDBMSG= SYSDBRC=0 SYS_SQL_IP_ALL=0 SYS_SQL_IP_STMT= select MAX(TXT_1."CLOSE_DT") from SASDDS.X_CLOSE_SYSTEM_CONTROL TXT_1 where TXT_1."SOURCE_SYSTEM_CD" = 'ICB' UPD_PROC_DTTM=TO_DATE('05Feb18:12:05:00','DDMonYY:HH24:MI:SS') UPD_TO_DTTM=TO_DATE('01Feb18:23:59:59','DDMonYY:HH24:MI:SS') USERDDS=SASDDS USERIDEA="sasidea" USERLDDQ="sasadm" USRCASH=sasusr _APSLIST=_CLIENT,_RESULT,_ODSDEST,_PROGRAM,_ENCODING,_METAUSER,_ODSSTYLE,_USERNAME,NRO_TARJETA,_CLIENTNAME,_METAFOLDER,_METAPERSON,_ ODSOPTIONS,_GOPT_DEVICE,_ARCHIVE_PATH,_ODSSTYLESHEET,_SECUREUS ERNAME,FECHA_CONSULTA_MAX,FECHA_CONSULTA_MIN,FECHA_CONSULTA_MAX_LABEL,FECHA_CONSULTA_MIN_LABEL _ARCHIVE_PATH=TEMPFILE _CLIENT=SAS Enterprise Guide; CLR 4.0.30319.42000; Microsoft Windows NT 6.1.7601 Service Pack 1 _CLIENTNAME=SAS Enterprise Guide _ENCODING=UTF8 _GOPT_DEVICE=ACTIVEX _METAFOLDER=/Delitos Electronicos/Procesos Almacenados/ _METAPERSON=U40621 _METAUSER=U40621 _ODSDEST=tagsets.sasreport13 _ODSOPTIONS=options(rolap="on") ATTRIBUTES=("CODEBASE"="http://www2.sas.com/codebase/graph/v94/sasgraph.exe#version=9,4") _ODSSTYLE=HtmlBlue _ODSSTYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/6.1/Styles/HtmlBlue.css") _PROGRAM=/Delitos Electronicos/Procesos Almacenados/750_SAS _REPLAY="&_URL?_sessionid=00000000-0000-0000-0000-000000000000&_program=replay&_entry=&_TMPCAT.." _RESULT=PACKAGE_TO_ARCHIVE _SECUREUSERNAME=(Process) _TMPCAT=APSWORK.TCAT0000 _USERNAME=(Process) NOTE: %INCLUDE (level 1) file /sasdata/U10564_Data/750_SAS.sas is file /sasdata/U10564_Data/750_SAS.sas. 2 +* Begin EG generated code (do not edit this line); 3 +* 4 +* Stored process registered by 5 +* Enterprise Guide Stored Process Manager V6.1 6 +* 7 +* ==================================================================== 8 +* Stored process name: 750_SAS 9 +* ==================================================================== 10 +* 11 +* Stored process prompt dictionary: 12 +* ____________________________________ 13 +* FECHA_CONSULTA 14 +* Type: Date range 15 +* Label: FECHA_CONSULTA 16 +* Attr: Visible 17 +* Desc: COLOQUE EL RANGO A CONSULTAR 18 +* ____________________________________ 19 +* NRO_TARJETA 20 +* Type: Text 21 +* Label: NRO_TARJETA 22 +* Attr: Visible 23 +* Desc: DIGITE EL NRO DE TARJETA A CONSULTAR 24 +* ____________________________________ 25 +*; 26 + 27 + 28 +*ProcessBody; 29 + 30 +%global FECHA_CONSULTA 31 + NRO_TARJETA; 32 + 33 +%STPBEGIN; 34 + 35 +/* Begin Librefs */ 36 +LIBNAME monfra SQLSVR READBUFF=32767 Datasrc=co01fraudedb_MONITORH SCHEMA=dbo USER=SasAPF PASSWORD=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX; NOTE: Libref MONFRA was successfully assigned as follows: Engine: SQLSVR The SAS System Physical Name: co01fraudedb_MONITORH 37 +/* End Librefs */ 38 + 39 +* End EG generated code (do not edit this line); 40 + 41 + 42 +/* --- Start of shared macro functions. --- */ 43 + 44 +/* Conditionally delete set of tables or views, if they exists */ 45 +/* If the member does not exist, then no action is performed */ 46 +%macro _eg_conditional_dropds /parmbuff; 47 + 48 + %local num; 49 + %local stepneeded; 50 + %local stepstarted; 51 + %local dsname; 52 + %local name; 53 + 54 + %let num=1; 55 + /* flags to determine whether a PROC SQL step is needed */ 56 + /* or even started yet */ 57 + %let stepneeded=0; 58 + %let stepstarted=0; 59 + %let dsname= %qscan(&syspbuff,&num,',()'); 60 + %do %while(&dsname ne); 61 + %let name = %sysfunc(left(&dsname)); 62 + %if %qsysfunc(exist(&name)) %then %do; 63 + %let stepneeded=1; 64 + %if (&stepstarted eq 0) %then %do; 65 + proc sql; 66 + %let stepstarted=1; 67 + 68 + %end; 69 + drop table &name; 70 + %end; 71 + 72 + %if %sysfunc(exist(&name,view)) %then %do; 73 + %let stepneeded=1; The SAS System 74 + %if (&stepstarted eq 0) %then %do; 75 + proc sql; 76 + 77 + %let stepstarted=1; 78 + %end; 79 + drop view &name; 80 + %end; 81 + %let num=%eval(&num+1); 82 + %let dsname=%qscan(&syspbuff,&num,',()'); 83 + %end; 84 + %if &stepstarted %then %do; 85 + quit; 86 + %end; 87 +%mend _eg_conditional_dropds; 88 + 89 +/* Build where clauses from stored process parameters */ 90 + 91 +%macro _eg_WhereParam( COLUMN, PARM, OPERATOR, TYPE=S, MATCHALL=_ALL_VALUES_, MATCHALL_CLAUSE=1, MAX= , IS_EXPLICIT=0); 92 + 93 + %local q1 q2 sq1 sq2; 94 + %local isEmpty; 95 + %local isEqual isNotEqual; 96 + %local isIn isNotIn; 97 + %local isString; 98 + %local isBetween; 99 + 100 + %let isEqual = ("%QUPCASE(&OPERATOR)" = "EQ" OR "&OPERATOR" = "="); 101 + %let isNotEqual = ("%QUPCASE(&OPERATOR)" = "NE" OR "&OPERATOR" = "<>"); 102 + %let isIn = ("%QUPCASE(&OPERATOR)" = "IN"); 103 + %let isNotIn = ("%QUPCASE(&OPERATOR)" = "NOT IN"); 104 + %let isString = (%QUPCASE(&TYPE) eq S or %QUPCASE(&TYPE) eq STRING ); 105 + %if &isString %then 106 + %do; 107 + %let q1=%str(%"); 108 + %let q2=%str(%"); 109 + %let sq1=%str(%'); 110 + %let sq2=%str(%'); 111 + %end; The SAS System 112 + %else %if %QUPCASE(&TYPE) eq D or %QUPCASE(&TYPE) eq DATE %then 113 + %do; 114 + %let q1=%str(%"); 115 + %let q2=%str(%"d); 116 + %let sq1=%str(%'); 117 + %let sq2=%str(%'); 118 + %end; 119 + %else %if %QUPCASE(&TYPE) eq T or %QUPCASE(&TYPE) eq TIME %then 120 + %do; 121 + %let q1=%str(%"); 122 + %let q2=%str(%"t); 123 + %let sq1=%str(%'); 124 + %let sq2=%str(%'); 125 + %end; 126 + %else %if %QUPCASE(&TYPE) eq DT or %QUPCASE(&TYPE) eq DATETIME %then 127 + %do; 128 + %let q1=%str(%"); 129 + %let q2=%str(%"dt); 130 + %let sq1=%str(%'); 131 + %let sq2=%str(%'); 132 + %end; 133 + %else 134 + %do; 135 + %let q1=; 136 + %let q2=; 137 + %let sq1=; 138 + %let sq2=; 139 + %end; 140 + 141 + %if "&PARM" = "" %then %let PARM=&COLUMN; 142 + 143 + %let isBetween = ("%QUPCASE(&OPERATOR)"="BETWEEN" or "%QUPCASE(&OPERATOR)"="NOT BETWEEN"); 144 + 145 + %if "&MAX" = "" %then %do; 146 + %let MAX = &parm._MAX; 147 + %if &isBetween %then %let PARM = &parm._MIN; 148 + %end; 149 + The SAS System 150 + %if not %symexist(&PARM) or (&isBetween and not %symexist(&MAX)) %then %do; 151 + %if &IS_EXPLICIT=0 %then %do; 152 + not &MATCHALL_CLAUSE 153 + %end; 154 + %else %do; 155 + not 1=1 156 + %end; 157 + %end; 158 + %else %if "%qupcase(&&&PARM)" = "%qupcase(&MATCHALL)" %then %do; 159 + %if &IS_EXPLICIT=0 %then %do; 160 + &MATCHALL_CLAUSE 161 + %end; 162 + %else %do; 163 + 1=1 164 + %end; 165 + %end; 166 + %else %if (not %symexist(&PARM._count)) or &isBetween %then %do; 167 + %let isEmpty = ("&&&PARM" = ""); 168 + %if (&isEqual AND &isEmpty AND &isString) %then 169 + &COLUMN is null; 170 + %else %if (&isNotEqual AND &isEmpty AND &isString) %then 171 + &COLUMN is not null; 172 + %else %do; 173 + %if &IS_EXPLICIT=0 %then %do; 174 + &COLUMN &OPERATOR %unquote(&q1)&&&PARM%unquote(&q2) 175 + %end; 176 + %else %do; 177 + &COLUMN &OPERATOR %unquote(%nrstr(&sq1))&&&PARM%unquote(%nrstr(&sq2)) 178 + %end; 179 + %if &isBetween %then 180 + AND %unquote(&q1)&&&MAX%unquote(&q2); 181 + %end; 182 + %end; 183 + %else 184 + %do; 185 + %local emptyList; 186 + %let emptyList = %symexist(&PARM._count); 187 + %if &emptyList %then %let emptyList = &&&PARM._count = 0; The SAS System 188 + %if (&emptyList) %then 189 + %do; 190 + %if (&isNotin) %then 191 + 1; 192 + %else 193 + 0; 194 + %end; 195 + %else %if (&&&PARM._count = 1) %then 196 + %do; 197 + %let isEmpty = ("&&&PARM" = ""); 198 + %if (&isIn AND &isEmpty AND &isString) %then 199 + &COLUMN is null; 200 + %else %if (&isNotin AND &isEmpty AND &isString) %then 201 + &COLUMN is not null; 202 + %else %do; 203 + %if &IS_EXPLICIT=0 %then %do; 204 + &COLUMN &OPERATOR (%unquote(&q1)&&&PARM%unquote(&q2)) 205 + %end; 206 + %else %do; 207 + &COLUMN &OPERATOR (%unquote(%nrstr(&sq1))&&&PARM%unquote(%nrstr(&sq2))) 208 + %end; 209 + %end; 210 + %end; 211 + %else 212 + %do; 213 + %local addIsNull addIsNotNull addComma; 214 + %let addIsNull = %eval(0); 215 + %let addIsNotNull = %eval(0); 216 + %let addComma = %eval(0); 217 + (&COLUMN &OPERATOR ( 218 + %do i=1 %to &&&PARM._count; 219 + %let isEmpty = ("&&&PARM&i" = ""); 220 + %if (&isString AND &isEmpty AND (&isIn OR &isNotIn)) %then 221 + %do; 222 + %if (&isIn) %then %let addIsNull = 1; 223 + %else %let addIsNotNull = 1; 224 + %end; 225 + %else The SAS System 226 + %do; 227 + %if &addComma %then %do;,%end; 228 + %if &IS_EXPLICIT=0 %then %do; 229 + %unquote(&q1)&&&PARM&i%unquote(&q2) 230 + %end; 231 + %else %do; 232 + %unquote(%nrstr(&sq1))&&&PARM&i%unquote(%nrstr(&sq2)) 233 + %end; 234 + %let addComma = %eval(1); 235 + %end; 236 + %end;) 237 + %if &addIsNull %then OR &COLUMN is null; 238 + %else %if &addIsNotNull %then AND &COLUMN is not null; 239 + %do;) 240 + %end; 241 + %end; 242 + %end; 243 +%mend; 244 +/* --- End of shared macro functions. --- */ 245 + 246 +/* --- Start of code for "FILTRO_COLUMNAS". --- */ 247 +%_eg_conditional_dropds(WORK.QUERY_FOR_EVENTOS750); 248 + 249 +PROC SQL; 250 + CREATE TABLE WORK.QUERY_FOR_EVENTOS750 AS 251 + SELECT t1.C07500, 252 + t1.C07503, 253 + t1.C07504, 254 + t1.C07553, 255 + t1.C07560, 256 + /* FECHA_TRX */ 257 + (INPUT (PUT(t1.C07507, char8.),yymmdd8.)) FORMAT=ddmmyys10. AS FECHA_TRX, 258 + t1.HORA, 259 + t1.C07508 FORMAT=BEST12., 260 + t1.C07510 FORMAT=BEST12., 261 + t1.C07543 FORMAT=BEST12., 262 + t1.C07511, 263 + t1.C07513 FORMAT=BEST12., The SAS System 264 + t1.C07514, 265 + t1.C07515, 266 + t1.C07516, 267 + t1.C07517, 268 + t1.C07518, 269 + t1.C07528, 270 + t1.C07519, 271 + t1.C07568, 272 + t1.C07569, 273 + t1.C07535, 274 + t1.C07521, 275 + t1.C07522, 276 + t1.C07524, 277 + t1.C07525, 278 + t1.C07577, 279 + t1.C07584, 280 + t1.C07585 FORMAT=BEST12., 281 + t1.C07589 FORMAT=BEST12., 282 + t1.C07590 FORMAT=BEST12., 283 + t1.C07592, 284 + t1.C07803, 285 + t1.C07530, 286 + t1.C07807 FORMAT=BEST12., 287 + t1.FRAUDE 288 + FROM MONFRA.EVENTOS750 t1 289 + WHERE %_eg_WhereParam( (CALCULATED FECHA_TRX), FECHA_CONSULTA_min, GE, TYPE=D, IS_EXPLICIT=0 ) AND 290 + %_eg_WhereParam( (CALCULATED FECHA_TRX), FECHA_CONSULTA_max, LT, TYPE=D, IS_EXPLICIT=0 ) AND t1.C07500 = 291 + "&NRO_TARJETA"; ERROR: The format CHAR was not found or could not be loaded. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 292 +QUIT; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.04 seconds cpu time 0.01 seconds 293 +/* --- End of code for "FILTRO_COLUMNAS". --- */ 294 + The SAS System 295 +/* --- Start of code for "PRESENTACION". --- */ 296 +%_eg_conditional_dropds(WORK.QUERY_FOR_EVENTOS750_0000); 297 + 298 +PROC SQL; 299 + CREATE TABLE WORK.QUERY_FOR_EVENTOS750_0000 AS 300 + SELECT t1.FRAUDE, 301 + t1.C07500 AS TARJETA, 302 + t1.C07503 AS MONTO_DOMINICANO, 303 + t1.C07504 AS MONTO_ORIGINAL, 304 + t1.C07553 AS IMPORTE_FACTURACION, 305 + t1.FECHA_TRX AS FECHA_TRX, 306 + t1.HORA, 307 + t1.C07508 FORMAT=BEST12. AS VTO, 308 + t1.C07510 FORMAT=BEST12. AS MCC, 309 + t1.C07543 FORMAT=BEST12. AS COD_PAIS, 310 + t1.C07511 AS ENTRY_MODE, 311 + t1.C07513 FORMAT=BEST12. AS ID_ADQ, 312 + t1.C07514 AS AUTORIZACION, 313 + t1.C07515 AS COD_RPTA, 314 + t1.C07516 AS ID_TERMINAL, 315 + t1.C07517 AS COD_COMERCIO, 316 + t1.C07518 AS COMERCIO, 317 + t1.C07528 AS LOCALIDAD_COMERCIO, 318 + t1.C07519 AS PAIS_COMERCIO, 319 + t1.C07568 AS CUPO_TARJETA, 320 + t1.C07522 AS TARJETA_EMPLEADO, 321 + /* CIF */ 322 + (case when substr(t1.C07524,1,8)='00000000' then substr(t1.C07524,7,10) else '00' ||substr(t1.C07524,1,8) 323 + end) AS CIF, 324 + t1.C07525 AS NOMBRE_CLIENTE, 325 + t1.C07577 AS APELLIDO, 326 + t1.C07584 AS CEDULA_CLIENTE, 327 + t1.C07585 FORMAT=BEST12. AS FECHA_NACIMIENTO, 328 + t1.C07589 FORMAT=BEST12. AS TELEFONO_RESIDENCIAL, 329 + t1.C07590 FORMAT=BEST12. AS TELEFONO_OFICINA, 330 + t1.C07592 AS TELEFONO_MOVIL, The SAS System 331 + t1.C07521 AS SITUACION_TARJETA, 332 + t1.C07569 AS ID_CTA_ORIGEN, 333 + t1.C07535 AS BIN, 334 + t1.C07803 AS STATUS_CUENTA, 335 + t1.C07530 AS COD_OFICIAL, 336 + t1.C07807 FORMAT=BEST12. AS ORG_CUENTA 337 + FROM WORK.QUERY_FOR_EVENTOS750 t1 338 + ORDER BY t1.FECHA_TRX; ERROR: File WORK.QUERY_FOR_EVENTOS750.DATA does not exist. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 339 +QUIT; 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 340 +/* --- End of code for "PRESENTACION". --- */ 341 + 342 +/* --- Start of code for "Create Format ($TARJETA - SASApp)". --- */ 343 +/* ------------------------------------------------------------------- 344 + Code generated by SAS Task 345 + 346 + Generated on: Monday, February 05, 2018 at 12:00:23 PM 347 + By task: Create Format ($TARJETA - SASApp) 348 + 349 + Input Data: .''n 350 + Server: 351 + ------------------------------------------------------------------- */ 352 + 353 +%_eg_conditional_dropds(SASDATA.CFMT); 354 + 355 +PROC FORMAT 356 + LIB=SASDATA 357 +; 358 + 358 !+ VALUE $TARJETA ( DEFAULT=19 ) The SAS System 359 + OTHER = "TARJETA"; NOTE: Format $TARJETA is already on the library. NOTE: Format $TARJETA has been written to SASDATA.FORMATS. 360 +RUN; NOTE: PROCEDURE FORMAT used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 361 + 362 +/* ------------------------------------------------------------------- 363 + End of task code. 364 + ------------------------------------------------------------------- */ 365 +RUN; QUIT; 366 +TITLE; FOOTNOTE; 367 + 368 +/* --- End of code for "Create Format ($TARJETA - SASApp)". --- */ 369 + 370 +/* --- Start of code for "750_SAS". --- */ 371 +/* --- Problem: Unable to express task in SAS code. --- */ 372 +/* --- End of code for "750_SAS". --- */ 373 + 374 +* Begin EG generated code (do not edit this line); 375 +;*';*";*/;quit; 376 +%STPEND; 386 + 387 +* End EG generated code (do not edit this line); 388 + NOTE: %INCLUDE (level 1) ending.