topic Re: Macro parameters for file names in SAS Programming

Macro parameters for file names

Babloo — Mon, 05 Oct 2015 07:52:53 GMT

I've to pass file names as macrometer and have to append the datasets once all the files are read.However, I'm experiencing the issue when filenames is greater than 1.

In the code below,filename resolves to SASApp_STPServer_2015-10-01_tmptcmsaslva2_18208.log and SASApp_STPServer_2015-10-04_tmptcmsaslva2_19142.log. These two .log files are different file and I was expecting SAS to process the file one-by-one and then need to consolidate to one master dataset log.log_analysis. When I ran the code below, SAS was trying to search both the file at the same moment and it failed as it could not find both the file in a single data step.

Could someone guide me how to achive this task? Please be informed that the macro filelist is also resolves to SASApp_STPServer_2015-10-01_tmptcmsaslva2_18208.log and SASApp_STPServer_2015-10-04_tmptcmsaslva2_19142.log

proc sql noprint;
select distinct fname into :filelist separated by ' '
from log.output_file;
quit;

%put &filelist;


%macro log_analysis
(filename =&filelist  /* Physical name of input file */
,data=work.log_analysis /* Name of SAS dataset */
,base=log.log_analysis /* Name of the Master dataset */
);

data &data;
set log.output_file;
length fname $400;
infile "/usr/sas/sas_config/Lev1/SASApp/StoredProcessServer/Logs/&filename" dsd truncover  END = end_of_file LRECL=32000;
fname="&filename" ;
DO WHILE (not end_of_file);
input var : $ 3000.;
var1 = _infile_;
if var1 = :'201' then do;
...........
...........
output;
end;
end;
run;

proc append data=&data base=&base force;
run;

%mend log_analysis;

data _null_;
  set log.output_file;
  if fname =: 'SASApp_STPServer' then call execute ('%log_analysis;');
else put 'no log files';
run;

Log snippet:

NOTE: CALL EXECUTE generated line.
1         + data work.log_analysis;  set log.output_file;    length fname $400;  infile 
"/usr/sas/sas_config/Lev1/SASApp/StoredProcessServer/Logs/SASApp_STPServer_2015-10-01_18208.log 
SASApp_STPServer_2015-10-04_19142.log" dsd truncover
2         + END = end_of_file LRECL=32000;  fname="SASApp_STPServer_2015-10-01_tmptcmsaslva2_18208.log 
SASApp_STPServer_2015-10-04_19142.log" ;  DO WHILE (not end_of_file);  input var : $ 3000.;    var1 = _infile_;  if 
var1 = :'201' then do;
3         + Date_TimeStamp= scan(var1,1," ");  Status = scan(var1,2," ");  Processid = scan(var1,3," ");  userid = scan(var1,4," 
");  Details = scan(var1,-1,'-');  output;  end;  end;  drop var var1;  run;

ERROR: Physical file does not exist, 
       /usr/sas/sas_config/Lev1/SASApp/StoredProcessServer/Logs/SASApp_STPServer_2015-10-01_18208.log 
       SASApp_STPServer_2015-10-04_19142.log.
NOTE: The SAS System stopped processing this step because of errors.

Re: Macro parameters for file names

RW9 — Mon, 05 Oct 2015 08:44:06 GMT

Hi,

You are mixing things up again.

%macro log_analysis (filename=,data=,base=);
  data &DATA.;
    length fname $400;
    infile "/usr/sas/sas_config/Lev1/SASApp/StoredProcessServer/Logs/&FILENAME." dsd truncover end=end_of_file lrecl=32000;
    input x $ y $;
  run;
  proc append data=&DATA. base=&BASE. force;
  run;
%mend log_analysis;

data _null_;
  set log.output_file;
  call execute (cats('%log_analysis(filename=',fname,',data=',fname,',base=base'));
run;

Lets look at the above code. First we define a macro. This is code that will be executed once per call. It accepts a fname, reads that file into a dataset, then appends it to a base dataset. Now we need to call this macro for a list of file names. Assuming you have a list of these in log.output_file, we can use the base characteristic of a dataset (which is a loop) to create the calls to the macro. You do not need to check if there any, because if there are 0 records in that dataset the call execute never gets called.

What you don't want to be doing is passing a large amount of data (filenames) via macro paramters. Firstly there are limits on what macro parameters can hold, secondly the code for this just gets needlessly messy and complicated.

As mentioned several times on these posts, write your code in Base SAS first for one element. In the above case this would be the datastep with no macro parameters. Once that is there and it works, then identify which parts of the code would need to change each run. These become your parameters. Then you need to decide how the call will be made, in this instance we have a datastep we will use as a loop. Remember, SAS Macro is not a real programming language, it is a text generator. Base is the programming language, macro is there to assist in not typing things out many times, so work out the Base code needed, then wrap it.

Just to add, consistent indetation, code formatting etc. makes code far easier to read.

Re: Macro parameters for file names

jakarman — Mon, 05 Oct 2015 09:09:31 GMT

Babloo go back to the design of the process. The reason:

- You cannot specify external filenames in the same way als doing that wiht tables/datsets.

- The translation to the physical has to be doen at some moment

This leaves you to the choice of:

a/ processing all files in a location using: FILENAME=variable specifies a variable that SAS sets to the physical name of the currently opened input file. The wildcards or perl-regular string like should make that easy (dates!).

http://support.sas.com/documentation/cdl/en/hostunx/67929/HTML/default/viewer.htm#p1cycu6ky2lsd7n0zqaskousxy5y.htm

b/ Define a list of files in a data-set table and process those using: FILEVAR=variable specifies a variable whose change in value causes the INFILE statement to close the current input file and open a new one.

https://support.sas.com/documentation/cdl/en/lestmtsref/68024/HTML/default/viewer.htm#n1rill4udj0tfun1fvce3j401plo.htm

As you see your log the specifyng off several names is done like a single name.

You could define a list (concatenation) that way. http://support.sas.com/documentation/cdl/en/hostunx/67929/HTML/default/viewer.htm#n066409vu4xdwfn17tf89hhlb5xh.htm it s more handy for code versioning wiht releasemanagement as for processsing data.

Re: Macro parameters for file names

Babloo — Mon, 05 Oct 2015 09:21:21 GMT

I got the following error as follows after I modified my original code as per your suggetsion. There is only one file name in 'log.output_file'

53         data _null_;
54           set log.output_file;
55           call execute (cats('%log_analysis(filename=',fname,',data=',fname,',base=base'));
56         run;

56       !     
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
MLOGIC(LOG_ANALYSIS):  Beginning execution.
ERROR: Macro parameter contains syntax error.
MLOGIC(LOG_ANALYSIS):  Ending execution.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 1 observations read from the data set LOG.OUTPUT_FILE.

I'm not familar with call execute syntax and it is little confusing to me. May I request you to guide me to come past this error?

Code:

%macro log_analysis
(filename =  /* Physical name of input file */
,data= /* Name of SAS dataset */
,base= /* Name of the Master dataset */
);

%let filedate=%sysfunc(putn("&sysdate9"d-1,yymmdd10.));
%put &filedate;

data &data;
set log.output_file;
length fname $400;
infile "/usr/sas/sas_config/Lev1/SASApp/StoredProcessServer/Logs/&filename" dsd truncover  END = end_of_file LRECL=32000;
fname="&filename" ;
/*DO WHILE (not end_of_file);*/
input var : $ 3000.;
var1 = _infile_;
if var1 = :'201' then do;
Date_TimeStamp= scan(var1,1," ");
Status = scan(var1,2," ");
Processid = scan(var1,3," ");
userid = scan(var1,4," ");
Details = scan(var1,-1,'-');
output;
end;
/*end;*/
drop var var1;
run;

proc append data=&data base=&base force;
run;

%mend log_analysis;

data _null_;
  set log.output_file;
  call execute (cats('%log_analysis(filename=',fname,',data=',fname,',base=base'));
run;

Once this code runs for one file, can I uncomment the 'do while' loop and 'end' in the datastep to run for multiple files and create a master (base) dataset?

Re: Macro parameters for file names

jakarman — Mon, 05 Oct 2015 12:37:38 GMT

Noticing your are trying to analyse a file that has been created from the ARM logging framework to create a table format.

Did you realize the same logging framework can be configured to write all data in a RDBMS (SAS/Share) dataset?

Re: Macro parameters for file names

Babloo — Mon, 05 Oct 2015 13:08:46 GMT

If I've one file, program is running perfectly. It is failing, If I've morethan one file as I told in my previous post.

Re: Macro parameters for file names

ballardw — Mon, 05 Oct 2015 15:04:11 GMT

Go back to your code without macros that works for one file.

Put both files in the place you have one file.

You likely will get the same errors.

That is what your macro is doing.

You have to provide the code to identify how many files are passed and then to process each of them separately.

Search the forum, there are multiple examples in different flavors of this.

Re: Macro parameters for file names

Babloo — Mon, 05 Oct 2015 17:47:39 GMT

May I request you to route me to any of those examples? I've trouble finding those threads.

Thanks!

Re: Macro parameters for file names

Babloo — Tue, 06 Oct 2015 01:30:42 GMT

I'm seeking for someone's guidance.

Re: Macro parameters for file names

Patrick — Tue, 06 Oct 2015 05:01:35 GMT

Why are there multiple threads from you with basically the same topic and none of these threads has been closed.

That makes things very messy and I don't feel like reading through all these extensive but similar threads to work out which answers you've already got and what could still be missing.

I believe you need to "re-start" and first make things work without macro coding. A starting point could be:

https://support.sas.com/documentation/cdl/en/lestmtsref/68024/HTML/default/viewer.htm#n1rill4udj0tfun1fvce3j401plo.htm

Only "macrotize" your solution once the base SAS stuff works.

Re: Macro parameters for file names

jakarman — Wed, 07 Oct 2015 09:10:13 GMT

Babloo, he must fundamental one. The admin con configure to store SP logging files content.

How the data for ARM can be stored directly to a tabel (exteren dbms / sas-share) is described in this nice blog post at: http://blogs.sas.com/content/sgf/2015/09/30/part-1-auditing-data-access-who-did-what-and-when/
There is no need to develop something for functionality that can be configured.

There is no need to use macro-processing only some fixed/moving values.

The PID number is part of filename as setup in the mentioned config-files. As it is a randomnumber it shoudl be masked/wild-carded.

See:

%let filedate=%sysfunc(putn("&sysdate9"d-1,yymmdd10.));
%put &filedate; /* this date lay-out can be put in the source later */

data &data;
set log.output_file;
length fname $400;
infile "/usr/sas/sas_config/Lev1/SASApp/StoredProcessServ?er/Logs/SASApp_STPServer_2015-10-04_*.log"
dsd truncover END = end_of_file LRECL=32000 filename=fname ;
input var : $ 3000.;
var1 = _infile_;
if var1 = :'201' then do;
Date_TimeStamp= scan(var1,1," ");
Status = scan(var1,2," ");
Processid = scan(var1,3," ");
userid = scan(var1,4," ");
Details = scan(var1,-1,'-');
output;
end;drop var var1;
run;

It will read all log files on that fixed date putting that in a dataset witt the fname having the switch for all read physical files.
Why overcomplicating that simple approach?

Reread the infile statement for the filename option. http://support.sas.com/documentation/cdl/en/lestmtsref/68024/HTML/default/viewer.htm#n1rill4udj0tfun1fvce3j401plo.htm