topic Re: SAS code to read multiple .log files to produce a dataset in SAS Programming

SAS code to read multiple .log files to produce a dataset

Babloo — Fri, 21 Aug 2015 11:59:06 GMT

I've a .log file (for example) as below.

Host: 'tmptcmsaslva2', OS: 'LIN X64', Release: '2.6.32-431.3.1.el6.x86_64', SAS Version: '9.03.01M2P08152012', Command: '/usr/sas/sas9.3/SASFoundation/9.3/sasexe/sas -noterminal -netencryptalgorithm SASProprietary -metaserver tmptcmsaslva2.timeinc.com -metaport 8561 -metarepository Foundation -objectserver -objectserverparms "protocol=bridge spawned spp=45106 cid=0 classfactory=15931E31-667F-11D5-8804-00C04F35AC8C server=OMSOBJ:SERVERCOMPONENT/A52GREI3.AV000006 cel=credentials dnsMatch=tmptcmsaslva2.timeinc.com multiuser port=8611 lb saslangrunas=client applevel=3"'

Log continued from /usr/sas/sas_config/Lev1/SASApp/StoredProcessServer/Logs/SASApp_STPServer_2015-08-19_tmptcmsaslva2_19142.log

2015-08-20T00:00:05,082 INFO [04086681] :sassr - New out call client connection (74065) for user

2015-08-20T00:00:05,092 INFO [04086681] :datapo@saspw - New client connection (74064) accepted from server port 8611 for SAS token user Encryption level is Credentials using encryption algorithm SASPROPRIETARY. Peer IP address and port are [10.176.232.41]:39494.

2015-08-20T00:00:05,094 INFO [04086689] 74064:datapo@saspw - STP: 36518: Creating New Context

In this log file I need to read the records from line number 6 and I need to produce the output as below. Please note that the field 'userid' is not a fixed width delimited. The value should be read before the hyphen -

Date_With_TimeStamp	Status	Processid	userid	Details
2015-08-20T00:00:05,082	INFO	[04086681]	:sassrv	New out call client connection (74065) for user
2015-08-20T00:00:05,092	INFO	[04086681]	:datapo@saspw	New client connection (74064) accepted from server port 8611 for SAS token user Encryption level is Credentials using encryption algorithm SASPROPRIETARY. Peer IP address and port are [10.176.232.41]:39494
2015-08-20T00:00:05,094	INFO	[04086689]	74064:datapo@saspw	STP: 36518: Creating New Context

I tried the code like below (to read one file) to accomplish this task. However I got struck when I tried to read multiple similar log files (from UNIX) to produce a dataset (consolidation of all files). The files are looks like SASApp_STP_2015-08-19_tmp1_19142.log, SASApp_STP_2015-08-19_tmp2_19142.log, SASApp_STP_2015-08-19_tmp3_19142.log

data log_analysis;

infile '/usr/sas/sas_config/Lev1/SASApp/Logs/SASApp_STP_2015-08-19_tmp1_19142.log' truncover;

input var : $ 3000.;

var1 = _infile_;

if var1 = :'2015';

Date_With_TimeStamp = scan(var1,1," ");

Status = scan(var1,2," ");

Processid = scan(var1,3," ");

userid = scan(var1,4," ");

Details = scan(var1,-1,'-');

drop var var1;

run;

I request someone to guide me to tweak this code in such a way to read any such log files with similar format.

Re: SAS code to read multiple .log files to produce a dataset

RW9 — Fri, 21 Aug 2015 12:21:30 GMT

Sorry, what is the question?

Re: SAS code to read multiple .log files to produce a dataset

Babloo — Fri, 21 Aug 2015 12:27:08 GMT

I've a code below to read one file. How to make this code to read multiple files with similar layout to produce one dataset?

data log_analysis;

infile '/usr/sas/sas_config/Lev1/SASApp/Logs/SASApp_STP_2015-08-19_tmp1_19142.log' truncover;

input var : $ 3000.;

var1 = _infile_;

if var1 = :'2015';

Date_With_TimeStamp = scan(var1,1," ");

Status = scan(var1,2," ");

Processid = scan(var1,3," ");

userid = scan(var1,4," ");

Details = scan(var1,-1,'-');

drop var var1;

run;

Re: SAS code to read multiple .log files to produce a dataset

data_null__ — Fri, 21 Aug 2015 13:08:35 GMT

/*Create sample data files*/
filename FT15F001 temp lrecl=1024; 
data _null_; 
   infile FT15F001 end=eof length=l;
   array line[6] $1024; 
   do i=1 by 1 while(not eof);
      input line $varying1024. l;
      end; 
   do filename='SASApp_STP_2015-08-19_tmp1_19142.log', 'SASApp_STP_2015-08-19_tmp2_19142.log', 'SASApp_STP_2015-08-19_tmp3_19142.log'; 
      filevar = catx('/','~',filename);
      file dummy filevar=filevar lrecl=1024; 
      do i = 1 to dim(line);
         l = length(line);
         put line $varying1024. l;
         end; 
      end;      
   stop; 
   parmcards4; 
 
Host: 'tmptcmsaslva2', OS: 'LIN X64', Release: '2.6.32-431.3.1.el6.x86_64', SAS Version: '9.03.01M2P08152012', Command: '/usr/sas/sas9.3/SASFoundation/9.3/sasexe/sas -noterminal -netencryptalgorithm SASProprietary -metaserver tmptcmsaslva2.timeinc.com -metaport 8561 -metarepository Foundation -objectserver -objectserverparms "protocol=bridge spawned spp=45106 cid=0 classfactory=15931E31-667F-11D5-8804-00C04F35AC8C server=OMSOBJ:SERVERCOMPONENT/A52GREI3.AV000006 cel=credentials dnsMatch=tmptcmsaslva2.timeinc.com multiuser port=8611 lb saslangrunas=client applevel=3"'
Log continued from /usr/sas/sas_config/Lev1/SASApp/StoredProcessServer/Logs/SASApp_STPServer_2015-08-19_tmptcmsaslva2_19142.log
2015-08-20T00:00:05,082 INFO  [04086681] :sassr - New out call client connection (74065) for user
2015-08-20T00:00:05,092 INFO  [04086681] :datapo@saspw - New client connection (74064) accepted from server port 8611 for SAS token user Encryption level is Credentials using encryption algorithm SASPROPRIETARY.  Peer IP address and port are [10.176.232.41]:39494.
2015-08-20T00:00:05,094 INFO  [04086689] 74064:datapo@saspw - STP: 36518: Creating New Context
;;;; 
   run; 

/*Use wildcard fileref to read the logs as one file, notice the asterisk*/
filename FT76F001 '~/SASApp_STP_2015-08-19_tmp*_19142.log' lrecl=1024; 
data log_analysis;
   length filename file $128; 
   retain filename;
   infile FT76F001 eov=eov truncover filename=file;
   input @; 
   if _n_ eq 1 or eov then do; /*file boundary*/     
      filename=file;
      eov=0; 
      input / / / @; 
      end; 
   input Date_With_TimeStamp :E8601DT. Status :$4. Processid :$12. userid:$16. dash :$1. details $1024.; 
   drop dash;
   format Date: E8601DT24.3; 
   run; 
proc print; 
   run; 

Re: SAS code to read multiple .log files to produce a dataset

RW9 — Fri, 21 Aug 2015 13:15:39 GMT

Well, there is a few ways.

Easiest is to change the infile:

infile "c:\temp\*.log" truncover;

Woulr read in all .log from that directory.

The other way I tend to do it is to pipe in a directory listing of where the logs are stored (I use windows, so you will need to change this to your Operating System):

filename pipe dirlist 'dir "c:\temp\*.log" /b';

data list;

infile dirlist;

run;

Gives a list of all files in that directory with .log extension. We can then generate the necessary code from there:

data _null_:

set list;

call execute('data log_analysis;

infile "c:\temp\'||strip(dirlist)||'.log" truncover;

input var : $ 3000.;

var1 = _infile_;

if var1 = :'2015';

Date_With_TimeStamp = scan(var1,1," ");

Status = scan(var1,2," ");

Processid = scan(var1,3," ");

userid = scan(var1,4," ");

Details = scan(var1,-1,'-');

drop var var1;

run;');

run;

Re: SAS code to read multiple .log files to produce a dataset

Babloo — Fri, 21 Aug 2015 14:06:31 GMT

My files are in UNIX.How to write the filename statement for files available in UNIX?

Re: SAS code to read multiple .log files to produce a dataset

RW9 — Fri, 21 Aug 2015 14:19:24 GMT

As I mention I work on Windows. Something like:

filename pipe dirlist 'ls ".../temp/*.log"';

Re: SAS code to read multiple .log files to produce a dataset

Babloo — Fri, 21 Aug 2015 14:29:07 GMT

Thanks for the code. Will your filename statement in the second set of code will read the files from UNIX?

What does this statement (input / / / @)does?

Thanks again.

Re: SAS code to read multiple .log files to produce a dataset

Tom — Fri, 21 Aug 2015 14:41:51 GMT

If you can use a simple wildcard pattern to find the files then just modify the name used in the INFILE statement to include the wildcard pattern. You might also want to add the FILENAME option so that SAS will return the actual name of the file that the current line comes from. You will need two variables since the one referenced on the INFILE statement will be dropped.

data log_analysis;

length fname filename $200;

infile '/usr/sas/sas_config/Lev1/SASApp/Logs/SASApp_STP_2015-08-19_*.log' truncover filename=fname;

input var : $ 3000.;

filename=fname;

var1 = _infile_;

...

If you just have a list of individual files whose names you already know you can just use a fileref that points to multiple files.

filename in

('/usr/sas/sas_config/Lev1/SASApp/Logs/SASApp_STP_2015-08-19_tmp1_19142.log'

,'/usr/sas/sas_config/Lev1/SASApp/Logs/SASApp_STP_2015-08-19_tmp2_19142.log'

,'/usr/sas/sas_config/Lev1/SASApp/Logs/SASApp_STP_2015-08-19_tmp3_19142.log'

);

data log_analysis;

length fname filename $200;

infile IN truncover filename=fname;

input var : $ 3000.;

filename=fname;

var1 = _infile_;

...

If you have a dataset with the list of filename then use that dataset and then use a DO loop to read the individual files.

data log_analysis;

set filelist ;

fname=filenamee ;

infile logfile filevar=fname truncover end=eof;

do while (not eof);

input var : $ 3000.;

var1 = _infile_;

...

end;

run;

Re: SAS code to read multiple .log files to produce a dataset

data_null__ — Fri, 21 Aug 2015 14:43:36 GMT

Babloo wrote:

Thanks for the code. Will your filename statement in the second set of code will read the files from UNIX? My OS is UNIX but you will need to supply the proper path and depending on the actual names of the files you may need something a bit different for the filename part. My example is "self-contained" you could copy and paste it to your SAS and it should run. I assume you have /home ~ directory.

What does this statement (input / / / @)does? Skips the records at the beginning of the file that you don't want to read.

Thanks again.

You may need to spend a few minutes reading the manual. You can't expect me to write your program if you are not going to do any work to learn how it words.

Re: SAS code to read multiple .log files to produce a dataset

data_null__ — Fri, 21 Aug 2015 14:45:14 GMT

You didn't look a my example, it uses wildcard to READ. I use FILEVAR to create test files.

Re: SAS code to read multiple .log files to produce a dataset

Tom — Fri, 21 Aug 2015 15:33:23 GMT

I updated my answer to include an example using a list of filenames from a dataset.

Re: SAS code to read multiple .log files to produce a dataset

jakarman — Fri, 21 Aug 2015 17:56:05 GMT

The header is indicating the ARM logging is active. That is where those INFO messages have their origin.
See SAS(R) Environment Manager 2.4: User's Guide and SAS(R) 9.4 Logging: Configuration and Programming Reference, Second Edition

Your question is about something doing yourself that is delivered as a service.

Re: SAS code to read multiple .log files to produce a dataset

Babloo — Mon, 24 Aug 2015 07:12:00 GMT

Can someone explain why dataset with created with 0 observations? See the log below.

3 data log_analysis;

4 length fname filename $200;

5 infile '/usr/sas/sas_config/Lev1/SASApp/StoredProcessServer/Logs/SASApp_STPServer_2014-11-04_tmptcmsaslva2_*.log'

5 ! truncover filename=fname;

6 input var : $ 3000.;

7 filename=fname;

8 var1 = _infile_;

9 if var1 = :'2015';

10 Date_With_TimeStamp = scan(var1,1," ");

11 Status = scan(var1,2," ");

12 Processid = scan(var1,3," ");

13 userid = scan(var1,4," ");

14 Details = scan(var1,-1,'-');

15 drop var var1;

16 run;

2 The SAS System 03:07 Monday, August 24, 2015

NOTE: The infile '/usr/sas/sas_config/Lev1/SASApp/StoredProcessServer/Logs/SASApp_STPServer_2014-11-04_tmptcmsaslva2_*.log' is:

Filename=/usr/sas/sas_config/Lev1/SASApp/StoredProcessServer/Logs/SASApp_STPServer_2014-11-04_tmptcmsaslva2_10891.log,

File List=/usr/sas/sas_config/Lev1/SASApp/StoredProcessServer/Logs/SASApp_STPServer_2014-11-04_tmptcmsaslva2_*.log,

Owner Name=sassrv,Group Name=sas,

Access Permission=rwxrwxr-x,

Last Modified=Tue Nov 4 17:05:00 2014,

File Size (bytes)=2148

NOTE: The infile '/usr/sas/sas_config/Lev1/SASApp/StoredProcessServer/Logs/SASApp_STPServer_2014-11-04_tmptcmsaslva2_*.log' is:

Filename=/usr/sas/sas_config/Lev1/SASApp/StoredProcessServer/Logs/SASApp_STPServer_2014-11-04_tmptcmsaslva2_11086.log,

File List=/usr/sas/sas_config/Lev1/SASApp/StoredProcessServer/Logs/SASApp_STPServer_2014-11-04_tmptcmsaslva2_*.log,

Owner Name=sassrv,Group Name=sas,

Access Permission=rwxrwxr-x,

Last Modified=Tue Nov 4 17:05:00 2014,

File Size (bytes)=2089

NOTE: The infile '/usr/sas/sas_config/Lev1/SASApp/StoredProcessServer/Logs/SASApp_STPServer_2014-11-04_tmptcmsaslva2_*.log' is:

Filename=/usr/sas/sas_config/Lev1/SASApp/StoredProcessServer/Logs/SASApp_STPServer_2014-11-04_tmptcmsaslva2_11215.log,

File List=/usr/sas/sas_config/Lev1/SASApp/StoredProcessServer/Logs/SASApp_STPServer_2014-11-04_tmptcmsaslva2_*.log,

Owner Name=sassrv,Group Name=sas,

Access Permission=rwxrwxr-x,

Last Modified=Tue Nov 4 17:05:00 2014,

File Size (bytes)=2089

NOTE: 15 records were read from the infile

'/usr/sas/sas_config/Lev1/SASApp/StoredProcessServer/Logs/SASApp_STPServer_2014-11-04_tmptcmsaslva2_*.log'.

The minimum record length was 83.

The maximum record length was 256.

NOTE: 15 records were read from the infile

'/usr/sas/sas_config/Lev1/SASApp/StoredProcessServer/Logs/SASApp_STPServer_2014-11-04_tmptcmsaslva2_*.log'.

The minimum record length was 83.

The maximum record length was 256.

NOTE: 15 records were read from the infile

'/usr/sas/sas_config/Lev1/SASApp/StoredProcessServer/Logs/SASApp_STPServer_2014-11-04_tmptcmsaslva2_*.log'.

The minimum record length was 83.

The maximum record length was 256.

One or more lines were truncated.

NOTE: The data set WORK.LOG_ANALYSIS has 0 observations and 6 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

Re: SAS code to read multiple .log files to produce a dataset

Babloo — Mon, 24 Aug 2015 07:15:41 GMT

Thanks for the code. However, please guide me to handle if any of the fields status, userid and processid were missing.

Re: SAS code to read multiple .log files to produce a dataset

Tom — Mon, 24 Aug 2015 11:43:26 GMT

I would assume because of line 9. None of the 45 records you read from the 3 files started with the four characters '2015'.

Perhaps there are leading spaces on the lines? Perhaps the lines are longer and are wrapping when you look at the files in your terminal or editor window and the '2015' is in the middle of the line?

Re: SAS code to read multiple .log files to produce a dataset

Babloo — Mon, 24 Aug 2015 12:59:46 GMT

I slightly tweaked my code to search for '201' and it worked. However, if there are any missing values in any of the variables (status,processid,userid), I don't know to handle.

Input File:

2014-11-04T17:04:59,710 INFO [00000009] :sassrv - Client connection 2 for user sassrv closed.

2014-11-04T17:04:59,905 DEBUG [00001133] 3:sassrv - START Perf.ARM.IOM.StoredProcessServer.ServerAdministration.DeferredStopServer 7fd96afe7290 0 0

2014-11-04T17:04:59,925 [00001133] 3:sassrv - STP: Stored Process Server Shutting Down.

My code:

data log_analysis;

length fname filename $200;

infile '/usr/sas/sas_config/Lev1/SASApp/Logs/SASApp_STPServer_2014-11-04_tmptcmsaslva2_*.log' dsd truncover filename=fname;

input var : $ 3000.;

filename=fname;

var1 = _infile_;

if var1 = :'201';

Date_With_TimeStamp = scan(var1,1," ");

Status = scan(var1,2," ");

Processid = scan(var1,3," ");

userid = scan(var1,4," ");

Details = scan(var1,-1,'-');

drop var var1;

run;

Output:

filename	Date_With_TimeStamp	Status	Processid	userid	Details
/usr/sas/sas_config/Lev1/SASApp/Logs/SASApp_STPServer_2014-11-04_tmptcmsaslva2_10891.log	2014-11-04T17:04:59,710	INFO	[00000009]	:sassrv	Client connection 2 for user sassrv closed.
/usr/sas/sas_config/Lev1/SASApp/Logs/SASApp_STPServer_2014-11-04_tmptcmsaslva2_10891.log	2014-11-04T17:04:59,905	DEBUG	[00001133]	3:sassrv	START Perf.ARM.IOM.StoredProcessServer.ServerAdministration.DeferredStopServer 7fd96afe7290 0 0
/usr/sas/sas_config/Lev1/SASApp/Logs/SASApp_STPServer_2014-11-04_tmptcmsaslva2_10891.log	2014-11-04T17:04:59,925	[00001133]	3:sassrv	-	STP: Stored Process Server Shutting Down.

Here for the third record, variable 'status' should be missing, but I got 'processid' value in the 'status' variable. Also I need only the file name in the variable 'filename' instead of full directory.

Desired Output:

filename	Date_With_TimeStamp	Status	Processid	userid	Details
SASApp_STPServer_2014-11-04_tmptcmsaslva2_10891.log	2014-11-04T17:04:59,710	INFO	[00000009]	:sassrv	Client connection 2 for user sassrv closed.
SASApp_STPServer_2014-11-04_tmptcmsaslva2_10891.log	2014-11-04T17:04:59,905	DEBUG	[00001133]	3:sassrv	START Perf.ARM.IOM.StoredProcessServer.ServerAdministration.DeferredStopServer 7fd96afe7290 0 0
SASApp_STPServer_2014-11-04_tmptcmsaslva2_10891.log	2014-11-04T17:04:59,925		[00001133]	3:sassrv	STP: Stored Process Server Shutting Down.

Thanks again for any help I receive.

Re: SAS code to read multiple .log files to produce a dataset

data_null__ — Mon, 24 Aug 2015 14:01:10 GMT

I think you may want to read the reply from @Jaap Karman

Babloo wrote:

Thanks for the code. However, please guide me to handle if any of the fields status, userid and processid were missing.

You can use FORMATTED INPUT for the first three fields.

/*Create sample data files*/
filename FT15F001 temp lrecl=1024; 
data _null_; 
   infile FT15F001 end=eof length=l;
   array line[9] $1024; 
   do i=1 by 1 while(not eof);
      input line $varying1024. l;
      end; 
   do filename='SASApp_STP_2015-08-19_tmp1_19142.log', 'SASApp_STP_2015-08-19_tmp2_19142.log', 'SASApp_STP_2015-08-19_tmp3_19142.log'; 
      filevar = catx('/','~',filename);
      file dummy filevar=filevar lrecl=1024; 
      do i = 1 to dim(line);
         l = length(line);
         put line $varying1024. l;
         end; 
      end;      
   stop; 
   parmcards4; 
 
Host: 'tmptcmsaslva2', OS: 'LIN X64', Release: '2.6.32-431.3.1.el6.x86_64', SAS Version: '9.03.01M2P08152012', Command: '/usr/sas/sas9.3/SASFoundation/9.3/sasexe/sas -noterminal -netencryptalgorithm SASProprietary -metaserver tmptcmsaslva2.timeinc.com -metaport 8561 -metarepository Foundation -objectserver -objectserverparms "protocol=bridge spawned spp=45106 cid=0 classfactory=15931E31-667F-11D5-8804-00C04F35AC8C server=OMSOBJ:SERVERCOMPONENT/A52GREI3.AV000006 cel=credentials dnsMatch=tmptcmsaslva2.timeinc.com multiuser port=8611 lb saslangrunas=client applevel=3"'
Log continued from /usr/sas/sas_config/Lev1/SASApp/StoredProcessServer/Logs/SASApp_STPServer_2015-08-19_tmptcmsaslva2_19142.log
2015-08-20T00:00:05,082 INFO  [04086681] :sassr - New out call client connection (74065) for user
2015-08-20T00:00:05,092 INFO  [04086681] :datapo@saspw - New client connection (74064) accepted from server port 8611 for SAS token user Encryption level is Credentials using encryption algorithm SASPROPRIETARY.  Peer IP address and port are [10.176.232.41]:39494.
2015-08-20T00:00:05,094 INFO  [04086689] 74064:datapo@saspw - STP: 36518: Creating New Context
2014-11-04T17:04:59,710 INFO  [00000009] :sassrv - Client connection 2 for user sassrv closed.
2014-11-04T17:04:59,905 DEBUG [00001133] 3:sassrv - START Perf.ARM.IOM.StoredProcessServer.ServerAdministration.DeferredStopServer 7fd96afe7290 0 0
2014-11-04T17:04:59,925       [00001133] 3:sassrv - STP: Stored Process Server Shutting Down.
;;;; 
   run; 

/*Use wildcard fileref to read the logs as one file*/
filename FT76F001 '~/SASApp_STP_2015-08-19_tmp*_19142.log' lrecl=1024; 
data log_analysis;
   length filename file $128; 
   retain filename;
   infile FT76F001 eov=eov truncover filename=file;
   input @; 
   if _n_ eq 1 or eov then do; /*file boundry*/     
      filename=file;
      eov=0; 
      input / / / @; 
      end; 
   input Date_With_TimeStamp E8601DT23. +1 Status $5. +1 Processid $10. userid:$16. dash :$1. details $1024.; 
   drop dash;
   format Date: E8601DT24.3; 
   run; 
proc print; 
   run; 

Re: SAS code to read multiple .log files to produce a dataset

data_null__ — Mon, 24 Aug 2015 15:04:10 GMT

You mentioned that USERID could be missing but did not show example data. I will assume that - (dash) still follows USERID field even if missing.

If we add DLM='-' to the INFILE statement we can read the first three fields with formatted input, read USERID using LIST/delimited input then switch back to formatted for DETAILS.

data log_analysis;
   length filename file $128; 
   retain filename;
   infile FT76F001 eov=eov truncover filename=file dlm='-'; 
   input @; 
   if _n_ eq 1 or eov then do; /*file boundry*/     
      filename=file;
      eov=0; 
      input / / / @; 
      end; 
   input Date_With_TimeStamp E8601DT23. +1 Status $5. +1 Processid $10. userid:$16. details $1024.; 
   format Date: E8601DT24.3; 
   run;

Re: SAS code to read multiple .log files to produce a dataset

Ron_MacroMaven — Tue, 25 Aug 2015 14:51:13 GMT

This tool is for Windows logs.

http://www.sascommunity.org/wiki/Reading_job_logs