topic Re: Importing multple excel file with a ist of diff names in SAS Programming

Importing multple excel file with a ist of diff names

thomask23 — Thu, 04 Apr 2019 17:29:06 GMT

I need help. I am new to macros. I need to create a macro that will import multiple excel files base on a list of names. Below is my current code. Please help.

%let dte = FEB2019;/*update monthly*/

/*Identify the current months FL files FILEDATE*/

filename fref pipe "ls /unixfolder/*&dte.*.xlsx";

data work.file_curr;

infile fref;

input ;

file = scan(_infile_,-1,'/');

path=scan(_infile_,1,'.');

run;

/*List of current xls files to import*/

data file_a(keep=file );

set file_curr;

RUN;

/*Macro to import files*/

options mprint symbolgen mlogic;

%macro Imp(all_mth);

%let k=1;

%let mth = %scan(&all_mth, &k);

%do %while ("&mth" NE "");

proc import DATAFILE="/unixlocation/&mth."

dbms=xlsx

out=outdata replace;

run;

proc append base=outdata _ALL data=outdata FORCE;

run;

%let k = %eval(&k + 1);

%let mth = %scan(&all_mth, &k);

%end;

%mend;

data _null_;

length check $200.;

set file_a;

check = '%imp(file="strip(file));");

Please help!

Re: Importing multple excel file with a ist of diff names

Reeza — Thu, 04 Apr 2019 17:48:01 GMT

Look into CALL EXECUTE instead of your last data step. Which step are you having issues with?

Re: Importing multple excel file with a ist of diff names

thomask23 — Thu, 04 Apr 2019 18:20:21 GMT

The program works if I manually update the macro. The problem is the naming conventions will change each month. I am attempting to create a macro to execute it as a macro using a datafile. however I do not know how to run a macro using a datafile.

Re: Importing multple excel file with a ist of diff names

Tom — Thu, 04 Apr 2019 18:25:37 GMT

Write a macro to read one file.

Use the list of files to generate a separate call to the macro for each file you want to read.

Re: Importing multple excel file with a ist of diff names

ballardw — Thu, 04 Apr 2019 18:47:21 GMT

Be prepared to have problems with Proc Append following a Proc Import step.

Each time you call Proc Import with XLSX files the procedure makes guesses about the variable types and lengths. In some cases the guesses may result in different variable types. So the append will fail because of mismatched data types.

If the length of a character variable changes you have a chance of having data truncated unless the very first file you import has the longest values for every variable that will ever occur.

Another issue depending on how consistent the source of the XLSX files may be, is that your variables have a chance of changing names from file to file. Which can lead to missing values in the appended data because an Excel column that had previously had a header like "Number of cases" changed to "Cases" or "# of Cases".

I once had a client that sent three files a week and they asked why we kept charging them programming fees to read the files. Even after explaining that changing the order of columns and/or the column headers required that additional programming the client could not provide a consistent file layout.

Re: Importing multple excel file with a ist of diff names

Reeza — Thu, 04 Apr 2019 20:12:41 GMT

@thomask23 wrote:
The program works if I manually update the macro. The problem is the naming conventions will change each month. I am attempting to create a macro to execute it as a macro using a datafile. however I do not know how to run a macro using a datafile.

Check the documentation for call execute. YOu can pass the filename to a macro and it will do it for you. I have a macro on my github page that does this, you can take a look at it here:

The list_files macro part you have an easier solution so use that,but the data step and macro should be similar to what you need.

https://github.com/statgeek/SAS-Tutorials/blob/master/Import_all_files_one_type