topic Re: How to pass file names as macro prameters? in SAS Programming

How to pass file names as macro prameters?

Babloo — Thu, 01 Oct 2015 06:10:21 GMT

I've a requirement to pass file names as argument to create a dataset. As of now, I've the code where I read file names from .txt file and then calling macro, but my managements wants me to pass file names as argument by considering the future requirements.

My Existing code will be like,

data log.output_file ;
  infile "&path/output_file.txt" firstobs=2 truncover ;
  input fname $400. ;
run;

%macro log_analysis;

data log.log_analysis;
set log.output_file;
filename=fname;
length fname $400;
infile dummy dsd truncover FILEVAR = fname END = end_of_file LRECL=32000;
DO WHILE (not end_of_file);
input var : $ 3000.;
..........
..........
%mend log_analysis;

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

With this code, I'm calling the macro based on the filename. Instead of that, I need to create a macro and then pass file names as argument.

Although I've referred the example 2 from the following,I could not suceed.

https://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000210819.htm

Kindly seeking someon'e help to achive this task.

Thanks!

Re: How to pass file names as macro prameters?

arodriguez — Thu, 01 Oct 2015 06:42:37 GMT

If I have understood it properly, you want to use a macro with a parameter. To do that you must do something like that


%macro import_file(parameter1);

*to use it you must do something like that;
"&path/&parameter1..TXT";

%mend;

%import_file(TEST);


%macro import_file(parameter1=);

*to use it you must do something like that;
"&path/&parameter1..TXT";

%mend;

%import_file(parameter1=TEST);

They look really similar, the first if you put multiples parameters, they must be given in the same order of the definition. In the second you could give values to parameter without matter about the order.

I recomend use the second one, because if you have a macro with 10 parameters is very difficult to remmember order.

Re: How to pass file names as macro prameters?

Babloo — Thu, 01 Oct 2015 06:50:30 GMT

In your second example, how will you give the filenames to parameter without hardcoding? Can't we get the file reference and file name from .txt file and pass it to the macro parameter?

Re: How to pass file names as macro prameters?

arodriguez — Thu, 01 Oct 2015 07:04:18 GMT

You mean how to give a array of files to import and avoid to execute proc import for each parameter? You could define a character as separator of the array (I use to use # because sometimes files have spaces in names).

Uou could do something like

%macro import_files(File_names=);

%local i actual_file;
%do i=1 %to %sysfunc(countw(&File_names));
  %let actual_file=%scan(&File_names,&i,%str(#));
  *Now you have a parameter Actual_file with the i-th file that could be use as macro parameter in a
  data _null_ or proc import;


%end;

%mend;

%import_files(File_names=File1#File2#OtherFile);

Re: How to pass file names as macro prameters?

Babloo — Thu, 01 Oct 2015 07:13:22 GMT

Thanks for your coninued support.

I can import the file via data step and I've the code to do that. I need to pass file names as parameter while I reading the file to create a dataset.

In the code below, could you please tell me the folder where it will search for the file?

Re: How to pass file names as macro prameters?

arodriguez — Thu, 01 Oct 2015 07:19:25 GMT

%macro import_files(File_names=);

%local i actual_file;
%do i=1 %to %sysfunc(countw(&File_names));
  %let actual_file=%scan(&File_names,&i,%str(#));  *Now you have a parameter Actual_file with the i-th file that could be use as macro parameter in a  data _null_ or proc import;

data log.output_file ;
  infile "&path/&actual_file..txt" firstobs=2 truncover ;
  input fname $400. ;
run;

%end;

%mend;%import_files(File_names=File1#File2#OtherFile);

I think that you want to do something like that, since it use the &path macro variable, it depend on want you put in this macro var

Re: How to pass file names as macro prameters?

Babloo — Thu, 01 Oct 2015 08:10:21 GMT

From the following line, it seems you're hard coding the file names.

%import_files(File_names=File1#File2#OtherFile);

If so, I would like to have a macro without any hardcoding.

Re: How to pass file names as macro prameters?

arodriguez — Thu, 01 Oct 2015 08:32:46 GMT

You coud use the macro of http://support.sas.com/kb/45/805.html This macro search all files in a directory


%macro drive(dir,ext); 

	%local filrf rc did memcnt name;
                                                                                                                                        
  %let filrf=mydir;                                                                                                                      
                                                                                                                                        
  /* Assigns the fileref of mydir to the directory and opens the directory */                                                                    
  %let rc=%sysfunc(filename(filrf,&dir));                                                                                                
  %let did=%sysfunc(dopen(&filrf));                                                                                                              
  /* Returns the number of members in the directory */                                                                   
  %let memcnt=%sysfunc(dnum(&did));                                                                                                      
                                                                                                                                        
   /* Loops through entire directory */                                                                                                  
   %do i = 1 %to &memcnt;                                                                                                                
    
     /* Returns the extension from each file */                                                                                                                                    
     %let name=%qscan(%qsysfunc(dread(&did,&i)),-1,.);                                                                                   
                                                                                                                                        
     /* Checks to see if file contains an extension */                                                                                     
     %if %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) %then %do;                                                                  
                                                                                                                                        
     /* Checks to see if the extension matches the parameter value */                                                                      
     /* If condition is true prints the full name to the log       */                                                                      
      %if (%superq(ext) ne and %qupcase(&name) = %qupcase(&ext)) or                                                                       
         (%superq(ext) = and %superq(name) ne) %then %do;                                                                                     
         		%qsysfunc(dread(&did,&i))                  
      %end;                                                                               
     %end;                                                                                                                               
   %end;                                                                                                                                                                                                                                                                   
  /* Closes the directory  */                                                                                                            
  %let rc=%sysfunc(dclose(&did));                                                                                                        
                                                                                                                                        
%mend drive; 

%let FILES=%drive(&path);;

This macro return names separated by spaces not by #, so adapt the file

Re: How to pass file names as macro prameters?

Babloo — Thu, 01 Oct 2015 08:50:25 GMT

Thanks a ton.

If I'm not wrong, I need to place the code at the begining, before I call the macro %log_analysis from my intial post. I think this time I need to call that macro with parameters something like below.

macro %log_analysis (&files);

Am I right?

Re: How to pass file names as macro prameters?

arodriguez — Thu, 01 Oct 2015 08:56:39 GMT

yes, something like
%macro %log_analysis(parameter1=&files);

Re: How to pass file names as macro prameters?

Tom — Thu, 01 Oct 2015 12:51:32 GMT

One of the nice features of SAS macros is that you can define parameters as positional and still call them by name. You just can't do the reverse.

So if I had a macro defined like this:

%macro divide(numerator,denominator);
...
%mend divide;

I could call it many ways:

%divide(10,5);
%divide(numerator=10,denominator=5);
%divide(denominator=5,numerator=10);
%divide(10,denominator=5);

Re: How to pass file names as macro prameters?

Steelers_In_DC — Thu, 01 Oct 2015 13:28:35 GMT

Does this get you what you are looking for?

proc sql noprint;
select distinct memname into :namelist separated by ' '
from dictionary.columns
where libname = 'WORK' and memname ne '_PRODSAVAIL';

%put &namelist;

Re: How to pass file names as macro prameters?

Babloo — Fri, 02 Oct 2015 16:11:11 GMT

I agree with you Tom.

However intially I don't know to dynamically pass the filenames as per my initial. I.e. without hardcoding the macro parameter values like yours.

You have any other solutions for my intial post before I try the solution suggested by others?

Re: How to pass file names as macro prameters?

Tom — Fri, 02 Oct 2015 16:51:31 GMT

I really am not sure I understand the original question.

If you have an existing piece of code and you want to change to a macro with parameters then just additify the pieces that change and make them parameters. Then in the code replace the original hardcoded text with the macro variable reference to the parameter.

So if you are talking about the adding a parameter to your original macro then the change should look like this.

%macro log_analysis(filename);

data log.log_analysis;
  length fname $400;
   infile "&filename" dsd truncover END = end_of_file LRECL=32000;
   fname="&filename" ;
  DO WHILE (not end_of_file);
    input var : $ 3000.;
..........
..........
%mend log_analysis;

Now this program will have a problem when called multiple times since the name of the data set is now hard coded. But you could also convert that to a parameter. Or you could add more complex logic to read thie current file and then append the result to a master dataset that would accumulate the data over multiple macro calls.

Re: How to pass file names as macro prameters?

Babloo — Sat, 03 Oct 2015 03:48:19 GMT

Thanks for your prompt reply.

May I request you to tell me to append the datasets after I modify the datasets names as parameters? E.g. I will get three datasets if the macro is called three times. How to append these three datasets as one master dataset?

Re: How to pass file names as macro prameters?

Tom — Sat, 03 Oct 2015 11:56:41 GMT

I would use PROC APPEND.

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

data &data;
  length fname $400;
   infile "&filename" dsd truncover END = end_of_file LRECL=32000;
   fname="&filename" ;
...
run;

proc append data=&data base=&base force;
run;
%mend log_analysis;

Re: How to pass file names as macro prameters?

Babloo — Sun, 04 Oct 2015 09:37:27 GMT

How does the macro variable &base will get resolve? Because it was declared in your example.

Thanks again for your continued support.

Re: How to pass file names as macro prameters?

Tom — Sun, 04 Oct 2015 19:24:49 GMT

A macro variable gets resolve when you reference with &. So %PUT &BASE will write the value of BASE to the log.

A macro variable gets assigned a value in a number of ways.

%let base=fred;

... call symputx('base','fred'); ...

... select 'fred' into :base ....

In the context of call a macro the parmeters (which are really just macro variables that are local to the macro) the value assigned is based on how you call it.

If you specify a value on the macro call , %mymacro(base=fred), then that is the value assigned.

If have defined it with a default value in the macro definition then if you call the macro wihout specifying a value it will use the defualt value.

%macro mymacro(base=george); %put &base; %mend mymacro;

 %mymacro; 
 george
 %mymacro(base=fred);
 fred

If you want to override the default and set the value to an empty string then include it in the macro call. %mymacro(base=);

Re: How to pass file names as macro prameters?

Babloo — Mon, 05 Oct 2015 13:05:38 GMT

I've another question. How will you pass values to the maco paramter 'filename'? I need to pass values from the other macro (filelist) to the macro parameter 'filename' instead of explicitly defining the values. I did tried like below,

In the code below,filename resolves to SASApp_STPServer_2015-10-01_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

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;

you 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

Log:


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.

Thanks for any help you offer me.