Hi RW9,   I have tried this code but I am encountering an error which I can't figure out. See below:   NOTE: CALL EXECUTE generated line. 1 + proc import out=LD_Births_TableDefinitiondatafile="LD_Births_TableDefinition.xlsx" sheet="Sheet1" getnames=yes;run; 2 The SAS System 16:44 Wednesday, June 15, 2016 ERROR: "LD_BIRTHS_TABLEDEFINITIONDATAFILE" is not a valid name.   I already tried to put a "''" between  compress(scan(_infile_,1)),'datafile="',strip(_infile_)  however, it's still fail. So for the mean time, I am still using the previous code I have. If you can help me to figure out what could be missing here, that would be great! 🙂 ... View more

Hi Guys!   Thanks for your help! I believe I was able to manage the coding and I used Reeza's coding from this link:   https://communities.sas.com/t5/SAS-Data-Management/Check-if-Multiple-Variables-exist-in-a-SAS-Dataset/m-p/186433/highlight/true   The codes that I made are as follows: 1. I created an import macro code to import the table definition and the csv files; 2. I created a macro input that has the infile statement inside which calls the naming convention of my source files 3. I used Reeza's code (with a little tweak for my need) for my validation. This validation macro checks the variable count for my data definition table and for csv file. It creates a report that if a variable is missing, the code will not continue to process the infile statement (macro input). Inside this macro I call the macro input statement.   Again, thank you so much for the help specially for those who gave inputs and ideas for this inquiry! 🙂     ... View more

Hi RW9! Thanks for the tip! That was also the approach I been thinking this past few days.    I've already created a three (3) data definition of the source files and did a coding how to import these excel files. Below is the code. 1. Filename statement to read the files in a directory 2. SQL statement to input the excel file names in &name and &name2 3. Import procedure   filename indata pipe 'dir <directory>\*.xlsx /b'; data LD_tbldef; length filename $100; infile indata truncover; /* infile statement for file names*/ input filename $100.; /* read the file names from the directory*/ filename2=scan(filename,1); /*creating filename2 variable to exclude .xlsx extension*/ x=_n_; /*iteration of the variables*/ call symput ('num_files',_n_); /* store the record number in a macro variable*/ *if find(filename,'.xlsx','i') /*get only .xlsx files*/; run; %put Macro '&num_files' resolves to >>> &num_files.; proc sql noprint; select filename into :name separated by '|' from LD_tbldef; select filename2 into :name2 separated by '|' from LD_tbldef; quit; %put Macro '&name' resolves to >>> &name; %put Macro '&name2' resolves to >>> &name2; %macro read_LD_TblDef; %do i = 1 %to &num_files.; %let j = %cmpres(%scan(&name.,&i.,'|')); %let k = %cmpres(%scan(&name2.,&i.,'|')); proc import out = libref.&k. datafile="M:\LeeA\DIA\&j." dbms=xlsx replace; sheet="Sheet1"; getnames = yes; run; %put i = &i.; %put Note: Macro '&j' resolves to >>> &j.; %put Note: Macro '&k' resolves to >>> &k.; %end; %mend; %read_LD_TblDef;   After this step, I will do now the validation part. 🙂   Thanks again! ... View more

Hi Xia Keshan, Thanks for your reply! I would definitely have a look this functions and hopefully to start my coding. ... View more

Thanks for your reply, mate! But, is there a way I can put everything into one SAS code -- importing the data definition, and then importing the first obs of the csv file and then validation. Once validated, the code will continue to load the complete csv into SAS data set. I am not sure how I can start this, but if not, I probably do it one code per steps. Thanks! ... View more

I have found an answer for the validation in this thread:   https://communities.sas.com/t5/SAS-Data-Management/Check-if-Multiple-Variables-exist-in-a-SAS-Dataset/td-p/186429   Once I have figure out how I can put the steps that I want into a single code, I will post it here. But if you have a better way how to code, kindly let me know.   Thanks! ... View more