topic Re: Add Excel File Name as Variable When Importing Multiple Excel Files in SAS Enterprise Guide

Add Excel File Name as Variable When Importing Multiple Excel Files

ncorbett — Thu, 26 Aug 2021 14:27:53 GMT

Hello all,

New to SAS here!

I have a folder in which I will be dropping Excel Files (.xls) files. This is the format in which they come to me. The files have a date name. They will like this 08262021.xls.

I am trying to add a variable called "Date_Ordered", and for each line of each file add the name of the file.

I've searched the depths of the internet to try to find a code that will do this, but I don't see anyone out there posting it.

Here is the code I've got so far, which will import each file:

PROC IMPORT OUT= PROMO.CONNECTRAW DATAFILE=
"\\KTCHQF1\PARTHOME\PARTS SALES & MARKETING\PROMO TRACKER\CONNECT\2021\RAW DATA\*.xls"
DBMS=XLS REPLACE;
NAMEROW=4;
DATAROW=5;
SHEET="PARTS REPORT";
getnames=no;

(what i want to add is:

Variable name is Order_Date and for each line of each file, give me the File name)
RUN;

Does anyone know what I can do to add a variable that will give me the file name for each line that I'm importing?

I've attached sample data, so when I import this file, I want under Order_Date, the data for each line from this file to say 08252021.

Please let me know if more info or clarification is needed. OR if there is already something out there that can help me. And thank you for your help!

Re: Add Excel File Name as Variable When Importing Multiple Excel Files

PhilC — Fri, 27 Aug 2021 11:54:22 GMT

I think you should think about performing multiple PROC IMPORTS, one for each Excel file. Our user @Tom has an excellent solution that you can template. First he shows us away to read the filenames in the folder. See the first part of the code here.

Solved: Re: macro to read all files together - SAS Support Communities

Then using the same technique as Tom, adapt your PROC IMPORT code into Tom's second part, as well as the code to add the columns.

There are many ways to skin this cat, mind you. I tend to use DOSUBL or CALL EXECUTE in the second part, Tomato-Tomaato. This is another level of programming, writing programs to write programs. Good luck.

Re: Add Excel File Name as Variable When Importing Multiple Excel Files

ncorbett — Mon, 30 Aug 2021 22:09:58 GMT

Hi PhilC,

Thanks I will let you know how it works!