topic Add file name to all sas datasets in SAS Programming

Add file name to all sas datasets

deblee73 — Fri, 29 Sep 2023 14:26:29 GMT

Hello.

I need to import all excel files in a directory (all have very different names) and on import or after import, I need to add a column to each file and populate it with the file name. So if I have say 10 excel files and each have a different name of the month as the file name, I want to import them all and add a column "month" and populate it with the file name.

This works but it's only one file at a time.

data list;
set list indsname=name;
tablename=scan(name,2);
run;

Re: Add file name to all sas datasets

Reeza — Fri, 29 Sep 2023 14:59:17 GMT

How are you importing the files/sheets?Add it in that step.

Re: Add file name to all sas datasets

deblee73 — Fri, 29 Sep 2023 15:17:22 GMT

Not sure if you need to see all the code but the first big chunk gets a list of all the Excel files in the directory.

Then, the import:

%macro import_file(path, file_name, dataset_name );

proc import

datafile="&path.\&file_name."

dbms=xlsx

out=&dataset_name;

run;

%mend;

%*Call macro once for each entry in the list table created from the %list_files() macro;

data _null_;

set list;

string = catt('%import_file(', dir, ', ', name,', ', compress(substr(the_name,1,15)), ');');

call execute (string);

run;

Not sure where to put it in the above. Honestly, it was taking a lot of google code until I got something to work and this is the last piece.

I honestly don't need the stop have the file named the same, I just need to fine name in a column. I found some come that named each file Test01, Test02 on import which is ok but it didn't put the file name in the file.

Re: Add file name to all sas datasets

Tom — Fri, 29 Sep 2023 15:30:12 GMT

So this code is assuming that each XLSX workbook only has ONE worksheet (or that you only want the first worksheet when it has more than one).

Just add the step to add the name into the macro.

%macro import_file(path, file_name, dataset_name );
proc import 
  datafile="&path.\&file_name."
  dbms=xlsx
  out=&dataset_name
;
run;
data &dataset_name;
   set &dataset_name;
   length file_name $100;
   file_name="&file_name";
run; 
%mend;

Re: Add file name to all sas datasets

deblee73 — Fri, 29 Sep 2023 16:35:46 GMT

Sa-Weeeet. It works. God bless you. Thank you so very much.