macro to query each sqlite database (using &filename) in a folder of multiple sqlite databases

[mstinson16]

Hello, I am trying to use a macro to iterate through multiple sqlite databases that are stored in a folder (the file names are stored in the "filelist" table). The query runs if I use each database name statically, but I'm having trouble using "&" to cycle through all of the files in an automated fashion. Does anyone have a solution to fix this syntax issue? Thank you! Monique
 

%macro macroName;
 
%do j=1 %to &num_files ;
data _null_;  
set file_list;  
if _n_=&j;   
call symput ('filein',fname);
call symput ('scen',scenario);
run; 

/* THIS WORKS:
filename QUERY pipe "c:\sqlite\sqlite3.exe C:\data\WF2\wf2\demandTables\chicago2015CT-Demand_updated.sqlite ""select * from freight_trips_with_path"" ";
*/

/* THIS DOES NOT WORK: */
filename QUERY pipe "c:\sqlite\sqlite3.exe C:\data\WF2\wf2\demandTables\&fname ""select * from freight_trips_with_path"" ";

%end; 
 
%mend macroName;

%macroName;

[Tom]

What values are setting to the macro variables? If the macro variable FNAME has the value:

%let fname=chicago2015CT-Demand_updated.sqlite;

then the two FILENAME statements are exactly the same.

Why are you including trailing spaces in the macro variable values by using the old style CALL SYMPUT() function instead of the newer CALL SYMPUTX() function?

[mstinson16]

Thank you for updating my knowledge of the CALL SYMPUTX()  function. That helped. 

My final solution is not as automated as I would have liked, but it did the job:

** make list of files to process ****;

filename indata pipe 'dir C:\input_data_directory /b';
 
data file_list;  
length fname $56;  
infile indata truncover;             /* infile statement for file names */  
input fname $56.;                    /* read the file names from the directory */  
call symput ('num_files',_n_);  /* store the record number in a macro variable */
run;

data file_list;
set  file_list;
attrib scenario length = $10;
if    index(fname, "2015")  then scenario = "2015baseCase";
else if  index(fname, "2025")  then do;
 if   index(fname, "A")    then scenario = "2025_A;
 else if index(fname, "B")   then scenario = "2025_B";
 else if index(fname, "C")   then scenario = "2025C";
 *...add more files...*;
end;
run;

** Set up path to read files from the input data directory ****;

%let path= C:\input_data_directory;

** Now iterate through files in input data directory ****;

%macro macroName;

%do j=1 %to &num_files ;
data _null_;  
set file_list;  
if _n_=&j;   
call symputx ('filein',fname);
call symputx ('scen',scenario);
%let scen=scenario;
%let fn=fname;
run;

/* create the desired SQLITE query */

filename QUERY pipe "c:\sqlite\sqlite3.exe &path\FirstPartOfFilename&scen.-LastPartOfFilename.sqlite ""select * from table_of_interest"" ";

/* execute the query for database j */

data results_ThisDB;
   infile QUERY delimiter='|' missover dsd lrecl=32767;
   length var1 8
        var2 8;
   input  var1 var2 ;
run;

/* create an identifier (i.e., scenario) to later identify the source database */

data results_ThisDB;
set   results_ThisDB;
attrib scenario length = $10;
scenario = "&scen";
run;

/* assemble the individual files into a single file */
%if &j=1 %then %do;  
data  results_All;    
set    results_ThisDB;  
run;
%end;
%else %do;  
data  results_All;    
set    results_All results_ThisDB;  
run;
%end;

%end;  /* end of do-loop with index j */
 
%mend macroName;

%macroName;