Hi
I also need this type of result. but format is FLOWNAME_TABLENAME_YEAR.MONTH.DAY_HOUR.MINUTE.SECONDS.log
I have tried this code but getting errors.
Please suggest how to do it properly.
I have mentioned my code and one sample log file for your reference.
log file example,
SAS CODE:
%LET TDATA = /sasconf/Config/Lev1/SASApp/BatchServer/Logs ;
%PUT &TDATA = TDATA;
filename pipedir pipe ' dir "/sasconf/Config/Lev1/SASApp/BatchServer/Logs"/s' lrecl=5000;
data indata (keep = fn date time ampm Y);
infile pipedir truncover;
input line $char1000.;
length directory $1000;
retain directory;
if line =' ' or
index(upcase(line),'<DIR>') or
left(upcase(line))=:'VOLUME' then
delete;
if left(upcase(line))=:'DIRECTORY OF' then
directory=left(substr(line,index(upcase(line),'DIRECTORY OF')+12));
if left(upcase(line))=:'DIRECTORY OF' then
delete;
date=input(substr(line,1,10),?? mmddyy10.);
TIME = Input(substr(line,12,5),??TIME.);
AMPM = Input(substr(line,18,2), $3.);
NAME = INPUT(SUBSTR(LINE,40,3),$4.);
NAME2 = INPUT(SUBSTR(LINE,60,7),$8.);
fn = INPUT(SUBSTR(LINE,40,31),$32.);
call
symput (
'num_files'
,_n_);
call symput (
'filein'
,fn);
format date mmddyy10.;
FORMAT TIME
TIME.;
Y=1;
IF DATE NE TODAY() THEN DELETE;
IF SCAN(FN,-1) NE 'log' THEN DELETE;
if fn = 'ERROR CHECK.log' then delete;
proc sort ;
by descending DATE TIME;
run;
DATA X;
SET INDATA;
BY Y;
RETAIN C ;
C=C+1;
IF FIRST.Y THEN C=1;
RUN;
data _null_;
set X;
if c = 1 then call symput('fn1',FN);
if c = 2 then call symput('fn2',FN);
if c = 3 then call symput('fn3',FN);
if c = 4 then call symput('fn4',FN);
if c = 5 then call symput('fn5',FN);
if c = 6 then call symput('fn6',FN);
if c = 7 then call symput('fn7',FN);
if c = 8 then call symput('fn8',FN);
if c = 9 then call symput('fn9',FN);
if c = 10 then call symput('fn10',FN);
if c = 11 then call symput('fn11',FN);
if c = 12 then call symput('fn12',FN);
if c = 13 then call symput('fn13',FN);
if c = 14 then call symput('fn14',FN);
if c = 15 then call symput('fn15',FN);
if c = 16 then call symput('fn16',FN);
if c = 17 then call symput('fn17',FN);
/*
if c = 18 then call symput('fn18',FN);
if c = 19 then call symput('fn19',FN);
if c = 20 then call symput('fn20',FN);
if c = 21 then call symput('fN21',FN);
*/
run;
Filename in_e "&tdata\&fn1";
data temp;
infile in_e;
input msg $ cont $200.;
run;
data temp1;
set temp;
x= "&tdata\&fn1" ;
if index(cont, "ERROR:" ) ge 1 then output;
run;
Scheduled Example :::: NOTE: Log file opened at Tue, 28 Nov 2023 07:15:02.257
1 The SAS System 07:15 Tuesday, November 28, 2023
NOTE: Copyright (c) 2016 by SAS Institute Inc., Cary, NC, USA.
NOTE: SAS (r) Proprietary Software 9.4 (TS1M7)
Licensed to ODISHA COMPUTER APPLICATION CENTRE-D&I, Site 70287995.
NOTE: This session is executing on the Linux 3.10.0-1062.el7.x86_64 (LIN X64) platform.
NOTE: Analytical products:
SAS/STAT 15.2
SAS/ETS 15.2
SAS/OR 15.2
SAS/QC 15.2
NOTE: Additional host information:
Linux LIN X64 3.10.0-1062.el7.x86_64 #1 SMP Thu Jul 18 20:25:13 UTC 2019 x86_64 Red Hat Enterprise Linux Server release 7.7
(Maipo)
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:
real time 1.08 seconds
cpu time 0.04 seconds
NOTE: Libref SASDATA successfully assigned from logical server.
NOTE: Libref WRSDIST successfully assigned from logical server.
NOTE: Libref WRSTEMP successfully assigned from logical server.
NOTE: Libref VALIB successfully assigned from logical server.
NOTE: Libref STPSAMP successfully assigned from logical server.
NOTE: Libref SOURCE successfully assigned from logical server.
NOTE: Libref STAGE successfully assigned from logical server.
NOTE: Libref CORE successfully assigned from logical server.
NOTE: Libref NODE successfully assigned from logical server.
NOTE: Libref REPORT successfully assigned from logical server.
NOTE: Libref SHAK successfully assigned from logical server.
NOTE: AUTOEXEC processing beginning; file is /sasconf/Config/Lev1/SASApp/BatchServer/autoexec.sas.
NOTE: AUTOEXEC processing completed.
2 The SAS System 07:15 Tuesday, November 28, 2023
1 /****************************************************************************
2 * Job: CORE_APPLICABLE_AMOUNT A5JXCDAZ.C00000D5 *
3 * Description: *
4 * *
5 * Metadata Server: 10.150.228.50 *
6 * Port: 8561 *
7 * Location: /04_Stage_To_Core_Jobs *
8 * *
9 * Server: SASApp A5JXCDAZ.AT000002 *
10 * *
11 * Source Tables: STG_APPL_AMT - Stage.STG_APPL_AMT A5JXCDAZ.BR00014V *
12 * STG_APPL_AMT_AJST - A5JXCDAZ.BR00014W *
13 * Stage.STG_APPL_AMT_AJST *
14 * Target Tables: CORE_APPL_AMT - Core.CORE_APPL_AMT A5JXCDAZ.BR000151 *
15 * CORE_APPL_AMT_AJST - A5JXCDAZ.BR000152 *
16 * Core.CORE_APPL_AMT_AJST *
17 * *
18 * Generated on: Wednesday, March 1, 2023 9:54:10 AM IST *
19 * Generated by: sasdemo *
20 * Version: SAS Data Integration Studio 4.905 *
21 ****************************************************************************/
22
23 /* Generate the process id for job */
24 %put Process ID: &SYSJOBID;
Process ID: 52142
25
26 /* General macro variables */
27 %let jobID = %quote(A5JXCDAZ.C00000D5);
28 %let etls_jobName = %nrquote(CORE_APPLICABLE_AMOUNT);
29 %let etls_userID = %nrquote(sasdemo);
30
31 /* Setup to capture return codes */
32 %global job_rc trans_rc sqlrc;
33 %let sysrc=0;
34 %let job_rc = 0;
35 %let trans_rc = 0;
36 %let sqlrc = 0;
37 %let syscc = 0;
38 %global etls_stepStartTime;
39 /* initialize syserr to 0 */
40 data _null_; run;
NOTE: DATA statement used (Total process time):
real time 0.10 seconds
cpu time 0.01 seconds
41
42 %macro rcSet(error);
43 %if (&error gt &trans_rc) %then
44 %let trans_rc = &error;
45 %if (&error gt &job_rc) %then
46 %let job_rc = &error;
47 %mend rcSet;
48
49 %macro rcSetDS(error);
50 if &error gt input(symget('trans_rc'),12.) then
51 call symput('trans_rc',trim(left(put(&error,12.))));
3 The SAS System 07:15 Tuesday, November 28, 2023
52 if &error gt input(symget('job_rc'),12.) then
53 call symput('job_rc',trim(left(put(&error,12.))));
54 %mend rcSetDS;
55
56 /* Create metadata macro variables */
57 %let IOMServer = %nrquote(SASApp);
58 %let metaPort = %nrquote(8561);
59 %let metaServer = %nrquote(sasdevdms.odishaminerals.gov.in);
60
61 /* Setup for capturing job status */
62 %let etls_startTime = %sysfunc(datetime(),datetime.);
63 %let etls_recordsBefore = 0;
64 %let etls_recordsAfter = 0;
65 %let etls_lib = 0;
66 %let etls_table = 0;
67
68 %global etls_debug;
69 %macro etls_setDebug;
70 %if %str(&etls_debug) ne 0 %then
71 OPTIONS MPRINT%str(;);
72 %mend;
73 %etls_setDebug;
74
75 /*==========================================================================*
76 * Step: Table Loader A5JXCDAZ.C3000CRV *
77 * Transform: Table Loader (Version 2.1) *
78 * Description: *
79 * *
80 * Source Table: STG_APPL_AMT - Stage.STG_APPL_AMT A5JXCDAZ.BR00014V *
81 * Target Table: CORE_APPL_AMT - Core.CORE_APPL_AMT A5JXCDAZ.BR000151 *
82 *==========================================================================*/
83
84 %let transformID = %quote(A5JXCDAZ.C3000CRV);
85 %let trans_rc = 0;
86 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.);
87
88 %let SYSLAST = %nrquote(Stage.STG_APPL_AMT);
89
90 %global etls_sql_pushDown;
91 %let etls_sql_pushDown = -1;
92 option DBIDIRECTEXEC;
93
94 %global etls_tableExist;
95 %global etls_numIndex;
96 %global etls_lastTable;
97 %let etls_tableExist = -1;
98 %let etls_numIndex = -1;
99 %let etls_lastTable = &SYSLAST;
100
101 /*---- Define load data macro ----*/
102
103 /* --------------------------------------------------------------
104 Load Technique Selection: Replace - Truncate
105 Constraint and index action selections: 'ASIS','ASIS','ASIS','ASIS'
106 Additional options selections...
107 Set unmapped to missing on updates: false
108 -------------------------------------------------------------- */
109 %macro etls_loader;
4 The SAS System 07:15 Tuesday, November 28, 2023
110
111 %let etls_tableOptions = ;
112
113 /* Determine if the target table exists */
114 %let etls_tableExist = %eval(%sysfunc(exist(Core.CORE_APPL_AMT, DATA)) or
115 %sysfunc(exist(Core.CORE_APPL_AMT, VIEW)));
116
117 /*---- Create a new table ----*/
118 %if (&etls_tableExist eq 0) %then
119 %do; /* if table does not exist */
120
121 %put %str(NOTE: Creating table ...);
122
123 data Core.CORE_APPL_AMT
124 (dbnull = (
125 INT_CIRCLE_ID = YES
126 INT_USER_ID = YES
127 INT_MODULE_ID = YES
128 VCH_REQ_ID = YES
129 STATUS = YES
130 INT_PAYMENT_MODE = YES
131 DEC_AMOUNT = YES
132 DTM_TRANS_DATE = YES));
133 attrib INT_CIRCLE_ID length = 8
134 format = 11.
135 informat = 11.
136 label = 'INT_CIRCLE_ID';
137 attrib INT_USER_ID length = 8
138 format = 11.
139 informat = 11.
140 label = 'INT_USER_ID';
141 attrib INT_MODULE_ID length = 8
142 format = 11.
143 informat = 11.
144 label = 'INT_MODULE_ID';
145 attrib VCH_REQ_ID length = 8
146 format = 20.
147 informat = 20.
148 label = 'VCH_REQ_ID';
149 attrib STATUS length = $8;
150 attrib INT_PAYMENT_MODE length = 8
151 format = 11.
152 informat = 11.
153 label = 'INT_PAYMENT_MODE';
154 attrib DEC_AMOUNT length = 8
155 format = 20.2
156 informat = 20.2
157 label = 'DEC_AMOUNT';
158 attrib DTM_TRANS_DATE length = 8
159 format = DATETIME22.3
160 informat = DATETIME22.3
161 label = 'DTM_TRANS_DATE';
162 call missing(of _all_);
163 stop;
164 run;
165
166 %rcSet(&syserr);
167
5 The SAS System 07:15 Tuesday, November 28, 2023
168 %end; /* if table does not exist */
169
170 %else
171 %do; /* table exists */
172 /*---- Truncate a table ----*/
173 %put %str(NOTE: Truncating table ...);
174 proc sql;
175 connect to ODBC
176 (
177 INSERTBUFF=32767 DATAsrc=mines_core AUTHDOMAIN="MSDB_Auth"
178 );
179 reset noprint;
180
181 execute
182 (
183 truncate table dbo.CORE_APPL_AMT
184 ) by ODBC;
185
186 %rcSet(&sqlrc);
187
188 disconnect from ODBC;
189 quit;
190
191 %rcSet(&sqlrc);
192
193 %end; /* table exists */
194
195 /*---- Append ----*/
196 %put %str(NOTE: Appending data ...);
197
198 proc append base = Core.CORE_APPL_AMT
199 data = &etls_lastTable (&etls_tableOptions) force ;
200 run;
201
202 %rcSet(&syserr);
203
204 %mend etls_loader;
205 %etls_loader;
NOTE: Truncating table ...
MPRINT(ETLS_LOADER): proc sql;
MPRINT(ETLS_LOADER): connect to ODBC ( INSERTBUFF=32767 DATAsrc=mines_core AUTHDOMAIN="MSDB_Auth" );
NOTE: Credential obtained from SAS metadata server.
MPRINT(ETLS_LOADER): reset noprint;
MPRINT(ETLS_LOADER): execute ( truncate table dbo.CORE_APPL_AMT ) by ODBC;
MPRINT(ETLS_LOADER): ;
MPRINT(ETLS_LOADER): disconnect from ODBC;
MPRINT(ETLS_LOADER): quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.08 seconds
cpu time 0.01 seconds
MPRINT(ETLS_LOADER): ;
NOTE: Appending data ...
MPRINT(ETLS_LOADER): proc append base = Core.CORE_APPL_AMT data = STAGE.STG_APPL_AMT () force ;
MPRINT(ETLS_LOADER): run;
NOTE: Appending STAGE.STG_APPL_AMT to CORE.CORE_APPL_AMT.
6 The SAS System 07:15 Tuesday, November 28, 2023
NOTE: There were 755 observations read from the data set STAGE.STG_APPL_AMT.
NOTE: 755 observations added.
NOTE: The data set CORE.CORE_APPL_AMT has . observations and 8 variables.
NOTE: PROCEDURE APPEND used (Total process time):
real time 0.36 seconds
cpu time 0.03 seconds
MPRINT(ETLS_LOADER): ;
206
207 %let etls_recnt = 0;
208 %macro etls_recordCheck;
209 %let etls_recCheckExist = %eval(%sysfunc(exist(Core.CORE_APPL_AMT, DATA)) or
210 %sysfunc(exist(Core.CORE_APPL_AMT, VIEW)));
211
212 %if (&etls_recCheckExist) %then
213 %do;
214 proc sql noprint;
215 select count(*) into :etls_recnt from Core.CORE_APPL_AMT;
216 quit;
217 %end;
218 %mend etls_recordCheck;
219 %etls_recordCheck;
MPRINT(ETLS_RECORDCHECK): proc sql noprint;
MPRINT(ETLS_RECORDCHECK): select count(*) into :etls_recnt from Core.CORE_APPL_AMT;
MPRINT(ETLS_RECORDCHECK): quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.09 seconds
cpu time 0.01 seconds
220
221
222
223 /** Step end Table Loader **/
224
225 /*==========================================================================*
226 * Step: Table Loader A5JXCDAZ.C3000CRW *
227 * Transform: Table Loader (Version 2.1) *
228 * Description: *
229 * *
230 * Source Table: STG_APPL_AMT_AJST - A5JXCDAZ.BR00014W *
231 * Stage.STG_APPL_AMT_AJST *
232 * Target Table: CORE_APPL_AMT_AJST - A5JXCDAZ.BR000152 *
233 * Core.CORE_APPL_AMT_AJST *
234 *==========================================================================*/
235
236 %let transformID = %quote(A5JXCDAZ.C3000CRW);
237 %let trans_rc = 0;
238 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.);
239
240 %let SYSLAST = %nrquote(Stage.STG_APPL_AMT_AJST);
241
242 %global etls_sql_pushDown;
243 %let etls_sql_pushDown = -1;
244 option DBIDIRECTEXEC;
245
246 %global etls_tableExist;
7 The SAS System 07:15 Tuesday, November 28, 2023
247 %global etls_numIndex;
248 %global etls_lastTable;
249 %let etls_tableExist = -1;
250 %let etls_numIndex = -1;
251 %let etls_lastTable = &SYSLAST;
252
253 /*---- Define load data macro ----*/
254
255 /* --------------------------------------------------------------
256 Load Technique Selection: Replace - Truncate
257 Constraint and index action selections: 'ASIS','ASIS','ASIS','ASIS'
258 Additional options selections...
259 Set unmapped to missing on updates: false
260 -------------------------------------------------------------- */
261 %macro etls_loader;
262
263 %let etls_tableOptions = ;
264
265 /* Determine if the target table exists */
266 %let etls_tableExist = %eval(%sysfunc(exist(Core.CORE_APPL_AMT_AJST, DATA)) or
267 %sysfunc(exist(Core.CORE_APPL_AMT_AJST, VIEW)));
268
269 /*---- Create a new table ----*/
270 %if (&etls_tableExist eq 0) %then
271 %do; /* if table does not exist */
272
273 %put %str(NOTE: Creating table ...);
274
275 data Core.CORE_APPL_AMT_AJST
276 (dbnull = (
277 INT_CIRCLE_ID = YES
278 INT_USER_ID = YES
279 VCH_REQ_ID = YES
280 INT_PAYMENT_MODE = YES
281 DEC_ADJUSTMENT_AMOUNT = YES
282 DTM_ADJUSTMENT_ON = YES
283 STATUS = YES));
284 attrib INT_CIRCLE_ID length = 8
285 format = 11.
286 informat = 11.
287 label = 'INT_CIRCLE_ID';
288 attrib INT_USER_ID length = 8
289 format = 11.
290 informat = 11.
291 label = 'INT_USER_ID';
292 attrib VCH_REQ_ID length = $40
293 format = $40.
294 informat = $40.
295 label = 'VCH_REQ_ID';
296 attrib INT_PAYMENT_MODE length = 8
297 format = 11.
298 informat = 11.
299 label = 'INT_PAYMENT_MODE';
300 attrib DEC_ADJUSTMENT_AMOUNT length = 8
301 format = 20.2
302 informat = 20.2
303 label = 'DEC_ADJUSTMENT_AMOUNT';
304 attrib DTM_ADJUSTMENT_ON length = 8
8 The SAS System 07:15 Tuesday, November 28, 2023
305 format = DATETIME22.3
306 informat = DATETIME22.3
307 label = 'DTM_ADJUSTMENT_ON';
308 attrib STATUS length = $8;
309 call missing(of _all_);
310 stop;
311 run;
312
313 %rcSet(&syserr);
314
315 %end; /* if table does not exist */
316
317 %else
318 %do; /* table exists */
319 /*---- Truncate a table ----*/
320 %put %str(NOTE: Truncating table ...);
321 proc sql;
322 connect to ODBC
323 (
324 INSERTBUFF=32767 DATAsrc=mines_core AUTHDOMAIN="MSDB_Auth"
325 );
326 reset noprint;
327
328 execute
329 (
330 truncate table dbo.CORE_APPL_AMT_AJST
331 ) by ODBC;
332
333 %rcSet(&sqlrc);
334
335 disconnect from ODBC;
336 quit;
337
338 %rcSet(&sqlrc);
339
340 %end; /* table exists */
341
342 /*---- Append ----*/
343 %put %str(NOTE: Appending data ...);
344
345 proc append base = Core.CORE_APPL_AMT_AJST
346 data = &etls_lastTable (&etls_tableOptions) force ;
347 run;
348
349 %rcSet(&syserr);
350
351 %mend etls_loader;
352 %etls_loader;
NOTE: Truncating table ...
MPRINT(ETLS_LOADER): proc sql;
MPRINT(ETLS_LOADER): connect to ODBC ( INSERTBUFF=32767 DATAsrc=mines_core AUTHDOMAIN="MSDB_Auth" );
NOTE: Credential obtained from SAS metadata server.
MPRINT(ETLS_LOADER): reset noprint;
MPRINT(ETLS_LOADER): execute ( truncate table dbo.CORE_APPL_AMT_AJST ) by ODBC;
MPRINT(ETLS_LOADER): ;
MPRINT(ETLS_LOADER): disconnect from ODBC;
MPRINT(ETLS_LOADER): quit;
NOTE: PROCEDURE SQL used (Total process time):
9 The SAS System 07:15 Tuesday, November 28, 2023
real time 0.02 seconds
cpu time 0.01 seconds
MPRINT(ETLS_LOADER): ;
NOTE: Appending data ...
MPRINT(ETLS_LOADER): proc append base = Core.CORE_APPL_AMT_AJST data = STAGE.STG_APPL_AMT_AJST () force ;
MPRINT(ETLS_LOADER): run;
NOTE: Appending STAGE.STG_APPL_AMT_AJST to CORE.CORE_APPL_AMT_AJST.
NOTE: There were 174 observations read from the data set STAGE.STG_APPL_AMT_AJST.
NOTE: 174 observations added.
NOTE: The data set CORE.CORE_APPL_AMT_AJST has . observations and 7 variables.
NOTE: PROCEDURE APPEND used (Total process time):
real time 0.12 seconds
cpu time 0.01 seconds
MPRINT(ETLS_LOADER): ;
353
354 %let etls_recnt = 0;
355 %macro etls_recordCheck;
356 %let etls_recCheckExist = %eval(%sysfunc(exist(Core.CORE_APPL_AMT_AJST, DATA)) or
357 %sysfunc(exist(Core.CORE_APPL_AMT_AJST, VIEW)));
358
359 %if (&etls_recCheckExist) %then
360 %do;
361 proc sql noprint;
362 select count(*) into :etls_recnt from Core.CORE_APPL_AMT_AJST;
363 quit;
364 %end;
365 %mend etls_recordCheck;
366 %etls_recordCheck;
MPRINT(ETLS_RECORDCHECK): proc sql noprint;
MPRINT(ETLS_RECORDCHECK): select count(*) into :etls_recnt from Core.CORE_APPL_AMT_AJST;
MPRINT(ETLS_RECORDCHECK): quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.09 seconds
cpu time 0.01 seconds
367
368
369
370 /** Step end Table Loader **/
371
372 %let etls_endTime = %sysfunc(datetime(),datetime.);
373
NOTE: SAS Institute Inc., SAS Campus Drive, Cary, NC USA 27513-2414
NOTE: The SAS System used:
real time 2.89 seconds
cpu time 0.25 seconds
... View more