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;
... View more
