topic Extract Errors and Warnings from log in ODS and Base Reporting

Extract Errors and Warnings from log

WouterKBC — Thu, 01 Sep 2016 14:10:02 GMT

I'm trying to extract errors and warnings from the log file. There are a lot of options to suppress messages, but I want both the full log as the warnings/errors.

Obviously I could run some regex on the logfile with some script, but I want this to happen in SAS.

Currently the full log is being written to an output file with

dm 'log; file "&out_dir\saslog.log" replace';

So either I can import this log file again and run some regex commands or, preferably, directly extract warnings and errors from the log file.

Is there an option in the log command or some other command to extract warnings/errors?

Thanks!

Re: Extract Errors and Warnings from log

Shmuel — Thu, 01 Sep 2016 14:59:49 GMT

log is a text file, so you can write a small SAS program to read it and look for some strings, like:

data my_log;

infile "&out_dir\saslog.log" truncover;

input a_line $200.;

if index(a_line, 'ERROR:') > 0 or

index(a_line, 'WARNING') > 0

then output;

run;

Depending on your system beaware of upcase/lowcase while defining strings to look for.

Re: Extract Errors and Warnings from log

WouterKBC — Thu, 01 Sep 2016 15:57:08 GMT

Awesome!

But doesn't this solution only work for single lines? What if the error/warning is printed over multiple lines?

Re: Extract Errors and Warnings from log

Shmuel — Thu, 01 Sep 2016 16:25:21 GMT

The program will sellect all the lines that contain the strings you scaned for.

Better try it and see the results. Addapt to the strings you are interested in.

Again - be aware of case: write the strings exactly as they apear in the log.

Re: Extract Errors and Warnings from log

Shmuel — Thu, 01 Sep 2016 18:28:21 GMT

You are right. This will bring one line per string found.

Anyway, I have the feeling that there is no way to know how many lines hold the message.

You can decide that you select 2 or more lines any time a string is found;

%let lines = 2;

data my_log;

infile "&out_dir\saslog.log" truncover;

input a_line $200.;

if index(a_line, 'ERROR:') > 0 or

index(a_line, 'WARNING') > 0

then do;

count = &lines;

do until (count=0);

output;

count-1;

if count > 0 then input a_line $200.;

end;

run;

Re: Extract Errors and Warnings from log

WouterKBC — Fri, 02 Sep 2016 08:32:06 GMT

That's what I expected. Thank you for the answer. We'll work with this solution for now.

If I come up with a better solution, I'll post it here. 🙂

Re: Extract Errors and Warnings from log

mstacey_foresters_com — Thu, 15 Sep 2016 13:47:32 GMT

This is what I do:

1) Pull all Logs from Folder

2) Convert to Table

3) Look for ERROR and remove known issues

4) Combine Tables

4) Create Run Log Email

%LET TDATA = U:\SAS\Morning logs\ ;
%PUT &TDATA = TDATA;



filename pipedir pipe ' dir "U:\SAS\Morning 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;

Results Attached

Re: Extract Errors and Warnings from log

Cynthia_sas — Sun, 18 Sep 2016 13:41:32 GMT

Hi:
Check for user group papers like this one http://analytics.ncsu.edu/sesug/2008/CC-037.pdf on the topic. Writing a SAS program to read and/or summarize what's in the log is something that has been written about a lot.

cynthia

Re: Extract Errors and Warnings from log

smenon — Tue, 11 Aug 2020 01:08:30 GMT

Hi,

This is great , thank you for sharing,

Couple of questions:

1) When i run this in my log folder, the errors only seem to be appearing for the first log file - How do i add more records (for multiple log files) to the temp1 table? or for example if i have 22 log files to parse, do i have to create 22 volatile tables? If you could share an example with 2-3 files, that would be great.

2) It looks like you are extracting the kick off time of the log (or start time of the program) , is there a way to extract the completion or last updated time ?

3) If i have a known error that will appear each time the code (ex:ERROR: Script file interpretation terminated due to error.) , is there any way to ignore these message

Thanks in advance,

Shashank

Re: Extract Errors and Warnings from log

Shakti_Sourav — Thu, 28 Dec 2023 06:35:10 GMT

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

Re: Extract Errors and Warnings from log

Kurt_Bremser — Thu, 28 Dec 2023 08:41:28 GMT

Do not use the backslash in a UNIX path. The backslash is used by the UNIX shell to mask an eventual special meaning of the immediately following character.

Re: Extract Errors and Warnings from log

Shakti_Sourav — Thu, 28 Dec 2023 09:25:47 GMT

Thanks for the quick reply.

Still, I am getting the same error after changing the slash.

Please find the screenshot for your reference.

Re: Extract Errors and Warnings from log

Kurt_Bremser — Thu, 28 Dec 2023 10:45:37 GMT

Please READ your log. You still have multiple backslashes in there; in fact it looks like you mistakenly put the path twice in there.

Go back to the start. Write one DATA step to read one log file, without the use of any macro code or automation. Then expand from there.

Re: Extract Errors and Warnings from log

Shakti_Sourav — Thu, 28 Dec 2023 10:56:48 GMT

No, It's not working. Showing error in &FN1.

Kindly find the attached screenshot.

Re: Extract Errors and Warnings from log

Kurt_Bremser — Thu, 28 Dec 2023 11:12:41 GMT

You don't read what I'm telling you. Without ANY macro code. No macro variables.

Once you have that working, decide which parts need to be static, semi-static (like a path coming from a global macro variable), or dynamic (like the filename).

Then proceed to replace the fixed code with macro variables, and test your code at every step, so you can weed out any ERROR right when you made the mistake. Doing it all at once leaves you with the mess you're in at the moment.

Re: Extract Errors and Warnings from log

Kurt_Bremser — Thu, 28 Dec 2023 11:14:17 GMT

And stop posting pictures. Copy/paste the log text into a window opened with this button:

Text allows us to make edits and annotations.

Re: Extract Errors and Warnings from log

Patrick — Thu, 28 Dec 2023 11:27:08 GMT

Is your SAS server OS Windows or Unix/Linux?

Right now you're using a Windows DIR command with a Unix path....

Your log shows that macro variable &fn1 doesn't exist and because you're using it as part of a path the path won't be valid even after you fix the rest of the path.

Not sure why you're creating all these macro variables &fn<n> given that you later on only try to use &fn1 ...which obviously doesn't get created.

If you really need all these macro variables then replace this incomplete list of IF statements with code like below.

....but first you need to get your logic right.