topic Re: macro to query each sqlite database (using &filename) in a folder of multiple sqlite dat in SAS Programming

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

mstinson16 — Mon, 21 Oct 2019 19:17:40 GMT

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;

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

Tom — Mon, 21 Oct 2019 19:36:36 GMT

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?

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

mstinson16 — Thu, 31 Oct 2019 16:52:40 GMT

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;

topic Re: macro to query each sqlite database (using &amp;filename) in a folder of multiple sqlite dat in SAS Programming

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

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

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

topic Re: macro to query each sqlite database (using &filename) in a folder of multiple sqlite dat in SAS Programming