BookmarkSubscribeRSS Feed
WouterKBC
Obsidian | Level 7

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!

16 REPLIES 16
Shmuel
Garnet | Level 18

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.

WouterKBC
Obsidian | Level 7

Awesome!

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

Shmuel
Garnet | Level 18

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.

Shmuel
Garnet | Level 18

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;

   end;

run;

 

WouterKBC
Obsidian | Level 7

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. 🙂

mstacey_foresters_com
Fluorite | Level 6

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

 

 

 


Run Log Check Email.JPG
Cynthia_sas
SAS Super FREQ
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
smenon
Calcite | Level 5

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

Shakti_Sourav
Quartz | Level 8

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.

Shakti_Sourav_0-1703744831876.png

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

 

Kurt_Bremser
Super User

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.

Shakti_Sourav
Quartz | Level 8

Thanks for the quick reply.

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

Please find the screenshot for your reference.

 

Shakti_Sourav_0-1703755547012.png

 

Kurt_Bremser
Super User

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.

Shakti_Sourav
Quartz | Level 8

Hi

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

Kindly find the attached screenshot.

Shakti_Sourav_0-1703761008497.png

 

Kurt_Bremser
Super User

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.

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 16 replies
  • 19318 views
  • 1 like
  • 8 in conversation