topic Re: scanning a log file and creating results in New SAS User

scanning a log file and creating results

VDD — Thu, 01 Nov 2018 23:59:50 GMT

I would like to extract the record count, dataset name, year and month from log files into a SAS dataset.

An example of a log after SAS has finished processing is below. How can I achieve the requested information by examine saved logs.

Thank you for your assistance.

14180 proc sql ;
14181 /* get all the file names into a macro var */
14182 select
14183 trim(libname) || '.' || memname into :dataset_vars separated by ' '
14184 from dictionary.tables
14185 where upcase(libname)="TEST"
14186 and upcase(memname) like 'COMPARE_%'
14187 ;
14188
14189 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 8.62 seconds
cpu time 0.82 seconds

14190
14191 data RESULTS;
14192 set &dataset_vars;* (drop=month);
14193 run;

NOTE: There were 812 observations read from the data set TEST.COMPARE_199910.
NOTE: There were 808 observations read from the data set TEST.COMPARE_199911.
NOTE: There were 620 observations read from the data set TEST.COMPARE_199912.
NOTE: There were 876 observations read from the data set TEST.COMPARE_200001.
NOTE: There were 868 observations read from the data set TEST.COMPARE_200002.
NOTE: There were 764 observations read from the data set TEST.COMPARE_200003.
NOTE: There were 708 observations read from the data set TEST.COMPARE_200004.
NOTE: There were 924 observations read from the data set TEST.COMPARE_200005.
NOTE: There were 1300 observations read from the data set TEST.COMPARE_200006.
NOTE: There were 868 observations read from the data set TEST.COMPARE_200007.
NOTE: There were 1056 observations read from the data set TEST.COMPARE_200008.
NOTE: There were 1952 observations read from the data set TEST.COMPARE_200009.
NOTE: There were 1040 observations read from the data set TEST.COMPARE_200010.
NOTE: There were 860 observations read from the data set TEST.COMPARE_200011.
NOTE: There were 620 observations read from the data set TEST.COMPARE_200012.
NOTE: There were 1264 observations read from the data set TEST.COMPARE_200101.
NOTE: There were 880 observations read from the data set TEST.COMPARE_200102.
NOTE: There were 1104 observations read from the data set TEST.COMPARE_200103.
NOTE: There were 1060 observations read from the data set TEST.COMPARE_200104.
NOTE: There were 1040 observations read from the data set TEST.COMPARE_200105.
NOTE: There were 1220 observations read from the data set TEST.COMPARE_200106.
NOTE: There were 437464 observations read from the data set TEST.COMPARE_200107.
NOTE: There were 439933 observations read from the data set TEST.COMPARE_200108.
NOTE: There were 438241 observations read from the data set TEST.COMPARE_200109.
NOTE: There were 421961 observations read from the data set TEST.COMPARE_200110.
NOTE: There were 394679 observations read from the data set TEST.COMPARE_200111.
NOTE: There were 373612 observations read from the data set TEST.COMPARE_200112.
NOTE: There were 1072 observations read from the data set TEST.COMPARE_200201.
NOTE: There were 968 observations read from the data set TEST.COMPARE_200202.
NOTE: There were 1044 observations read from the data set TEST.COMPARE_200203.
NOTE: There were 1088 observations read from the data set TEST.COMPARE_200204.
NOTE: There were 972 observations read from the data set TEST.COMPARE_200205.
NOTE: There were 1728 observations read from the data set TEST.COMPARE_200206.
NOTE: There were 1468 observations read from the data set TEST.COMPARE_200207.
NOTE: There were 1280 observations read from the data set TEST.COMPARE_200208.
NOTE: There were 1652 observations read from the data set TEST.COMPARE_200209.
NOTE: There were 1208 observations read from the data set TEST.COMPARE_200210.
NOTE: There were 1032 observations read from the data set TEST.COMPARE_200211.
NOTE: There were 712 observations read from the data set TEST.COMPARE_200212.
NOTE: There were 912 observations read from the data set TEST.COMPARE_200301.
NOTE: There were 852 observations read from the data set TEST.COMPARE_200302.
NOTE: There were 1032 observations read from the data set TEST.COMPARE_200303.
NOTE: There were 1092 observations read from the data set TEST.COMPARE_200304.
NOTE: There were 952 observations read from the data set TEST.COMPARE_200305.
NOTE: There were 1636 observations read from the data set TEST.COMPARE_200306.
NOTE: There were 1316 observations read from the data set TEST.COMPARE_200307.
NOTE: There were 1212 observations read from the data set TEST.COMPARE_200308.
NOTE: There were 1696 observations read from the data set TEST.COMPARE_200309.
NOTE: There were 1212 observations read from the data set TEST.COMPARE_200310.
NOTE: There were 852 observations read from the data set TEST.COMPARE_200311.
NOTE: There were 820 observations read from the data set TEST.COMPARE_200312.
NOTE: There were 944 observations read from the data set TEST.COMPARE_200401.
NOTE: There were 1048 observations read from the data set TEST.COMPARE_200402.
NOTE: There were 1332 observations read from the data set TEST.COMPARE_200403.
NOTE: There were 1120 observations read from the data set TEST.COMPARE_200404.
NOTE: There were 1040 observations read from the data set TEST.COMPARE_200405.
NOTE: There were 1992 observations read from the data set TEST.COMPARE_200406.
NOTE: There were 1420 observations read from the data set TEST.COMPARE_200407.
NOTE: There were 1468 observations read from the data set TEST.COMPARE_200408.
NOTE: There were 1816 observations read from the data set TEST.COMPARE_200409.
NOTE: There were 1220 observations read from the data set TEST.COMPARE_200410.
NOTE: There were 1232 observations read from the data set TEST.COMPARE_200411.
NOTE: There were 1004 observations read from the data set TEST.COMPARE_200412.
NOTE: There were 1272 observations read from the data set TEST.COMPARE_200501.
NOTE: There were 1028 observations read from the data set TEST.COMPARE_200502.
NOTE: There were 1104 observations read from the data set TEST.COMPARE_200503.
NOTE: There were 1132 observations read from the data set TEST.COMPARE_200504.
NOTE: There were 1000 observations read from the data set TEST.COMPARE_200505.
NOTE: There were 1788 observations read from the data set TEST.COMPARE_200506.
NOTE: There were 1412 observations read from the data set TEST.COMPARE_200507.
NOTE: There were 1648 observations read from the data set TEST.COMPARE_200508.
NOTE: There were 1876 observations read from the data set TEST.COMPARE_200509.
NOTE: There were 1156 observations read from the data set TEST.COMPARE_200510.
NOTE: There were 1108 observations read from the data set TEST.COMPARE_200511.
NOTE: There were 916 observations read from the data set TEST.COMPARE_200512.
NOTE: There were 1224 observations read from the data set TEST.COMPARE_200601.
NOTE: There were 1292 observations read from the data set TEST.COMPARE_200602.
NOTE: There were 1420 observations read from the data set TEST.COMPARE_200603.
NOTE: There were 1396 observations read from the data set TEST.COMPARE_200604.
NOTE: There were 1300 observations read from the data set TEST.COMPARE_200605.
NOTE: There were 2264 observations read from the data set TEST.COMPARE_200606.
NOTE: There were 1708 observations read from the data set TEST.COMPARE_200607.
NOTE: There were 1672 observations read from the data set TEST.COMPARE_200608.
NOTE: There were 2392 observations read from the data set TEST.COMPARE_200609.
NOTE: There were 1608 observations read from the data set TEST.COMPARE_200610.
NOTE: There were 1188 observations read from the data set TEST.COMPARE_200611.
NOTE: There were 1064 observations read from the data set TEST.COMPARE_200612.
NOTE: There were 1744 observations read from the data set TEST.COMPARE_200701.
NOTE: There were 1732 observations read from the data set TEST.COMPARE_200702.
NOTE: There were 1784 observations read from the data set TEST.COMPARE_200703.
NOTE: There were 1752 observations read from the data set TEST.COMPARE_200704.
NOTE: There were 1740 observations read from the data set TEST.COMPARE_200705.
NOTE: There were 2516 observations read from the data set TEST.COMPARE_200706.
NOTE: There were 2204 observations read from the data set TEST.COMPARE_200707.
NOTE: There were 2248 observations read from the data set TEST.COMPARE_200708.
NOTE: There were 2724 observations read from the data set TEST.COMPARE_200709.
NOTE: There were 1916 observations read from the data set TEST.COMPARE_200710.
NOTE: There were 1736 observations read from the data set TEST.COMPARE_200711.
NOTE: There were 1448 observations read from the data set TEST.COMPARE_200712.
NOTE: There were 1924 observations read from the data set TEST.COMPARE_200801.
NOTE: There were 1528 observations read from the data set TEST.COMPARE_200802.
NOTE: There were 1900 observations read from the data set TEST.COMPARE_200803.
NOTE: There were 1864 observations read from the data set TEST.COMPARE_200804.
NOTE: There were 1912 observations read from the data set TEST.COMPARE_200805.
NOTE: There were 3476 observations read from the data set TEST.COMPARE_200806.
NOTE: There were 2432 observations read from the data set TEST.COMPARE_200807.
NOTE: There were 2384 observations read from the data set TEST.COMPARE_200808.
NOTE: There were 3144 observations read from the data set TEST.COMPARE_200809.
NOTE: There were 1840 observations read from the data set TEST.COMPARE_200810.
NOTE: There were 1380 observations read from the data set TEST.COMPARE_200811.
NOTE: There were 1168 observations read from the data set TEST.COMPARE_200812.
NOTE: There were 960 observations read from the data set TEST.COMPARE_200901.
NOTE: There were 56 observations read from the data set TEST.COMPARE_200902.
NOTE: There were 4 observations read from the data set TEST.COMPARE_200903.
NOTE: There were 0 observations read from the data set TEST.COMPARE_200904.
NOTE: There were 0 observations read from the data set TEST.COMPARE_200905.
NOTE: There were 0 observations read from the data set TEST.COMPARE_200906.
NOTE: There were 0 observations read from the data set TEST.COMPARE_200907.
NOTE: There were 0 observations read from the data set TEST.COMPARE_200908.
NOTE: There were 0 observations read from the data set TEST.COMPARE_200909.
NOTE: There were 0 observations read from the data set TEST.COMPARE_200910.
NOTE: There were 0 observations read from the data set TEST.COMPARE_200911.
NOTE: There were 0 observations read from the data set TEST.COMPARE_200912.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201001.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201002.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201003.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201004.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201005.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201006.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201007.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201008.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201009.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201010.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201011.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201012.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201101.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201102.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201103.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201104.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201105.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201106.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201107.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201108.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201109.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201110.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201111.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201112.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201201.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201202.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201203.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201204.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201205.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201206.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201207.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201208.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201209.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201210.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201211.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201212.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201301.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201302.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201303.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201304.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201305.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201306.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201307.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201308.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201309.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201310.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201311.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201312.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201401.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201402.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201403.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201404.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201405.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201406.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201407.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201408.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201409.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201410.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201411.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201412.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201501.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201502.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201503.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201504.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201505.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201506.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201507.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201508.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201509.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201510.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201511.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201512.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201601.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201602.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201603.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201604.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201605.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201606.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201607.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201608.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201609.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201610.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201611.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201612.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201701.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201702.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201703.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201704.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201705.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201706.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201707.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201708.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201709.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201710.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201711.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201712.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201801.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201802.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201803.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201804.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201805.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201806.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201807.
NOTE: There were 0 observations read from the data set TEST.COMPARE_201808.
NOTE: The data set WORK.RESULTS has 2653462 observations and 33 variables.
NOTE: DATA statement used (Total process time):
real time 56.43 seconds
cpu time 2.25 seconds

Re: scanning a log file and creating results

Anthony45 — Fri, 02 Nov 2018 00:32:50 GMT

Sending the log to an external text file and then parsing it from there might be an option?

http://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/viewer.htm#a001330273.htm

Re: scanning a log file and creating results

Reeza — Fri, 02 Nov 2018 01:08:02 GMT

And take a quick look at PROC SCAPROC and see what it returns for you. For log parsing tools, search lexjansen.com for code samples, you may have to do some digging to find one that's suitable. You could also try searching on GitHub.

Re: scanning a log file and creating results

VDD — Fri, 02 Nov 2018 12:40:40 GMT

Thank you @Reeza and @Anthony45 for the suggestions.

While the proc scapproc may work well it currently doesn't fulfill my needs at least with my current understand of the process.

I did get this to work as a chucky dataset.

%let myfile "&path.\&file..log";
data junk;
	length junk $255. month $2. year $4. records 8.;
	infile &myfile.;
	input @1 junk $255.;
	test = substr(junk,1,17);
	if test = "NOTE: There were ";
	delims = ' ,.!:';               
	numWords = countw(junk, delims); 
        filename = scan(junk,numWords, delims);
	word2=scan(junk,4,delims);
	records = word2;
	delims = '_';
	numWords = countw(filename,delims);
	mon = scan(filename,numWords,delims);
	year = substr(mon,1,4);
	month = substr(mon,5,2);
	output;
	drop test mon junk word2 numwords delims;
run;

 proc print data=junk2;
 run;

Re: scanning a log file and creating results

Quentin — Fri, 02 Nov 2018 13:23:43 GMT

Since you're already reading the dictionary table to get the list of data set names, seems like it might be easier to use the dictionary table to create a data set with the names and record counts, rather than parse the log.

You don't need to upcase() libname or memname because they are stored upper case, and adding upcase() can sometimes slow down queries, because it can't use indexes (I believe, haven't tested recently).

create table want as
  select memname, nlobs
  from dictionary.tables
  where libname="TEST"
  and memname like "COMPARE_%"
;

Re: scanning a log file and creating results

VDD — Fri, 02 Nov 2018 15:49:06 GMT

Thank you @Quentin that would be perfect for those users whom have access to the datasets paths.

But, some users may not have data access, where those users roles may be to only read and process logs for them to create reports for auditing review.

What I have gets me started, but will need a lot of tailoring for future continues usage reading other log statements.

thank you all for your replies.

Re: scanning a log file and creating results

Quentin — Fri, 02 Nov 2018 16:41:47 GMT

Fair enough. On a related note, I wrote a paper about monitoring record counts in a SAS job, for similar QC. https://www.lexjansen.com/nesug/nesug11/ds/ds06.pdf

It's a macro, you pass it a list of data sets, and it gives you a little report on record counts.

%macro ReportObs
 (data= /*space delimited list of data set names*/
 ,out= /*optional name of output data set*/
 ,lib=work
 )
;
proc sql;
 %if %superq(out) ne %str() %then %do;
 create table &out as
 %end;
 select memname as Data
 ,memlabel as Label
 ,nlobs as nObs
 from dictionary.tables
 where libname=%upcase("&lib") and indexw (%upcase("&data"),memname)
 order by indexw (%upcase("&data"),memname)
 ;
quit;
%mend ReportObs;

It's designed to help detect when records have been accidentally deleted. But it's also useful just for writing a little human-readable report of key record counts at the end of a SAS job. I typically send the report to the log rather than output.

But yeah, if the goal is to show that the 'official' counts written by SAS in various log notes are correct, log parsing makes sense.