topic Re: How to extract variable from filename? in SAS Data Management

How to extract variable from filename?

pamplemousse822 — Wed, 15 May 2019 19:24:56 GMT

Hello,

I have a folder with ~3000 .xls files. I am trying to (1) import all into sas, and (2) extract each file name as values for a new variable.

I can accomplish (1) with the code below. Now, I am trying to modify this to extract each file name (_MMDDYYYY) as values for a new variable, date.

%macro impt(filename,i);
   proc import DATAFILE="&filename" OUT=_&i DBMS=xls REPLACE;
      run;
%mend impt;
%let path=D:\Folder\;
data _null_;
   command = "dir &path.*.xls /b";;
   infile dummy pipe filevar=command end=eof truncover;
   do i = 1 by 1 while(not eof);
      input path $128.;
      path=catt("&path.",path);
      put 'NOTE: ' path=;
      call execute(cats('%nrstr(%impt(',path,',',i,'));'));
      end;
   stop;
run;

I have tried including variations of the following code, but no new variable is created.

%macro impt(filename,i);
   proc import DATAFILE="&filename" OUT=_&i DBMS=xls REPLACE;
      run;
%mend impt;
%let path=D:\Folder\;
data _null_;
   length fname $200 ;
   command = "dir &path.*.xls /b";;
   infile  dummy pipe filevar=command end=eof truncover filename=fname;
   do i = 1 by 1 while(not eof);
      input path $128.;
      path=catt("&path.",path);
      put 'NOTE: ' path=;
      call execute(cats('%nrstr(%impt(',path,',',i,'));'));
	  call symputx('fname',fname);
      end;
   stop;
run;

How can I fix this? Thank you!

Re: How to extract variable from filename?

PaigeMiller — Wed, 15 May 2019 19:09:13 GMT

@pamplemousse822 wrote:

Hello,

I have a folder with ~3000 .xls files. I am trying to (1) import all into sas, and (2) extract each file name as values for a new variable.

I can accomplish (1) with the code below. Now, I am trying to modify this to extract each file name (_MMDDYYYY) as values for a new variable, date.
%macro impt(filename,i);
   proc import DATAFILE="&filename" OUT=_&i DBMS=xls REPLACE;
      run;
%mend impt;
%let path=D:\Folder\;
data _null_;
   command = "dir &path.*.xls /b";;
   infile dummy pipe filevar=command end=eof truncover;
   do i = 1 by 1 while(not eof);
      input path $128.;
      path=catt("&path.",path);
      put 'NOTE: ' path=;
      call execute(cats('%nrstr(%impt(',path,',',i,'));'));
      end;
   stop;
run;
I have tried including variations of the following code, but no new variable is created.
%macro impt(filename,i);
   proc import DATAFILE="&filename" OUT=_&i DBMS=xls REPLACE;
      run;
%mend impt;
%let path=D:\Filder\;
data _null_;
   length fname $200 ;
   command = "dir &path.*.xls /b";;
   infile  dummy pipe filevar=command end=eof truncover filename=fname;
   do i = 1 by 1 while(not eof);
      input path $128.;
      path=catt("&path.",path);
      put 'NOTE: ' path=;
      call execute(cats('%nrstr(%impt(',path,',',i,'));'));
	  call symputx('fname',fname);
      end;
   stop;
run;
How can I fix this? Thank you!

I think when you use FILENAME=, the variable named (in your case FNAME) is temporary, it disappears when the data step ends. So you have to assign it to a data step variable (for example, called FILENAME) that will be present when the data step ends.

filename=fname;

Re: How to extract variable from filename?

pamplemousse822 — Wed, 15 May 2019 19:25:57 GMT

hi, thank you

i moved it to above the infile line, still does not work. other ideas?

Re: How to extract variable from filename?

Tom — Wed, 15 May 2019 19:35:47 GMT

Where did you think that you created a new variable?

The only data step is a data _NULL_ step which does not create any datasets.

Re: How to extract variable from filename?

PaigeMiller — Wed, 15 May 2019 19:38:50 GMT

@pamplemousse822 wrote:
hi, thank you

i moved it to above the infile line, still does not work. other ideas?

Above the INFILE command the variable in FILENAME= has no value.

It should go after the INFILE command.

Re: How to extract variable from filename?

pamplemousse822 — Wed, 15 May 2019 19:39:54 GMT

All the new datasets are labeled _1 to _3000, I think from the out option in proc import. I don't fully understand how it works (found the code on another sas communities post) but it does import all files into the work directory. now, just need the date.

Re: How to extract variable from filename?

pamplemousse822 — Wed, 15 May 2019 19:44:56 GMT

%macro impt(filename,i);
   proc import DATAFILE="&filename" OUT=_&i DBMS=xls REPLACE;
      run;
%mend impt;
%let path=D:\Folder\;
data _null_;
   length fname $200 ;
   command = "dir &path.*.xls /b";;
   infile  dummy pipe filevar=command end=eof truncover filename=fname ;;
   filename=fname;
   do i = 1 by 1 while(not eof);
      input path $128.;
      path=catt("&path.",path);
      put 'NOTE: ' path=;
      call execute(cats('%nrstr(%impt(',path,',',i,'));'));
	  fname=fname;
      end;
   stop;
run;

Thank you.

I tried this, still no new variable. I also removed the filename=fname from infile statement.

Re: How to extract variable from filename?

Tom — Wed, 15 May 2019 19:47:01 GMT

@pamplemousse822 wrote:
All the new datasets are labeled _1 to _3000, I think from the out option in proc import. I don't fully understand how it works (found the code on another sas communities post) but it does import all files into the work directory. now, just need the date.

PROC IMPORT is not going to add any variables that aren't in the input file.

Looks like you want to update the macro to take a new parameter and then use that to add the variable.

%macro impt(filename,i,fname);
proc import DATAFILE="&filename" OUT=_&i DBMS=xls REPLACE;
run;
data _&i;
  set _&i;
  length fname $200;
  fname="&fname";
run;
%mend impt;

Then update the CALL EXECUTE to pass in that new value.

call execute(cats('%nrstr(%impt)(',path,',',i,',',fname,');'));

Re: How to extract variable from filename?

pamplemousse822 — Wed, 15 May 2019 20:04:59 GMT

Hello,

Thank you! It is almost working. Now since the actual file path is very long the variable, truncates. I only need the actual filename. How can I fix this? I think it is something that says only take the last 9 characters in the filename (_MMDDYYYY). But I am not sure where to put this in the code. Here is what I have so far.

%macro impt(filename,i,fname);
proc import DATAFILE="&filename" OUT=_&i DBMS=xls REPLACE;
run;
data _&i;
  set _&i;
  length fname $200;
  fname="&fname";
run;
%mend impt;
%let path=D:\Sindana\GreatRecession_PM\test\;
data _null_;
	length fname $1000 ;
   command = "dir &path.*.xls /b";;
   infile dummy pipe filevar=command end=eof truncover filename=fname;
   do i = 1 by 1 while(not eof);
      input path $128.;
      path=catt("&path.",path);
      put 'NOTE: ' path=;
      call execute(cats('%nrstr(%impt)(',path,',',i,',',fname,'));'));
      end;
   stop;
run;

Thank you!

Re: How to extract variable from filename?

Tom — Wed, 15 May 2019 20:39:10 GMT

Are the names valid names for datasets? If so I would just use the name of the file as the name of the dataset.

In that case you might not need to actually add the variable to the dataset as you could do that later when you USE the data.

data combine;
  set _01012019 _02012019 indsname=indsname;
  dsname=indsname;
run;

So something like this. Note that your data step was overly complex for the method you are using to get the filenames.

%macro impt(fname,path);
proc import DATAFILE="&path" OUT=&fname DBMS=xls REPLACE;
run;
%mend impt;

%let path=D:\xxxx\test\;
data _null_;
  infile "dir &path.*.xls /b" pipe truncover;
  input path $256. ;
  fname = scan(scan(path,-1,'\'),1,'.');
  call execute(cats('%nrstr(%impt)(fname=',fname,',path=',path,');'));
run;

You might need to make the logic that creates FNAME from the full PATH more complex. The logic above is just taking the part after the last folder name and then taking the part up to the first period.

So if path= 'D:\....\_01012019.xls' then fname will be '_01012019'.

Re: How to extract variable from filename?

pamplemousse822 — Wed, 15 May 2019 21:55:22 GMT

Thank you!!! It worked with the following code:

%macro impt(fname,path);
	proc import DATAFILE="&path" OUT=&fname DBMS=xls REPLACE;
	run;
%mend impt;

%let path=D:\Folder\;
data _null_;
	length fname $100;
   command = "dir &path.*.xls /b";;
   infile dummy pipe filevar=command end=eof truncover filename=fname;
   do i = 1 by 1 while(not eof);
      input path $128.;
      path=catt("&path.",path);
	  fname = scan(scan(path,-1,'\'),1,'.');
      call execute(cats('%nrstr(%impt)(fname=',fname,',path=',path,');'));
      end;
   stop;
run;

data combine;
  set _: indsname=indsname;
  date=substr(indsname, 7); 
run;

Re: How to extract variable from filename?

AngusLooney — Thu, 16 May 2019 09:41:36 GMT

The way I tackle this sort of thing, in DI Studio, is to run a UT/Macro that collects all the filenames and paths into a table, then use that table to control a Loop, cycling though the records in the "file listing" table, one at at time, which nicely gives you the current filename as a macro variable, which you pass to the infile statement, and use in the population of the table(s) you create from the file.

Equally, you can route file to various different "readers" based on assessing their nature by parsing their filenames, assuming there's a structure/schema in their naming (which there should be).

Easy to add the filename each record came from to the data.

Pretty standard to have data/metadata encoded into the filename, and simple to extract it from them, dates, file content types etc.