How to use filenames to create variables?

[marksanter]

Hello,

I have a few hundred filenames that look like:

1234A_file.xls

ABCDB_file.xls

CATSA_file.xls

I would like to take the first four characters to create a variable and the fifth character to create a separate variables at the beginning of my dataset. The dataset would look something like what is attached. 

I would greatly appreciate any guidance on how to achieve this.  

Mark

[Reeza]

From the variable values that's straightforward.

var1 = substr(variable, 1, 4);
var2 = substr(variable, 5, 1);

[marksanter]

Thank you for your help, will this pull the variable names from the file name itself? The current spreadsheets do not include the variables I need - those are found in the file names.  

Thank you,

Mark 

[Reeza]

I would assume you're capturing the filenames when importing your data. If you're not, then that is a different question and the answer depends on how you're importing your data. 

I don't think you explained your problem fully...

[Tom]

Much harder to do with EXCEL files than it would be with text files.  When reading text files you can use the FILENAME= option on the INFILE statement to access the name of the file being read.

You will need to use macro variable(s).

%let fname=1234A_file.xls;
proc import datafile="&fname" out=step1 replace dbsm=xls ;
run;
data want ;
   var1 = "%substr(&fname,1,5)";
   var2 = substr(var1,5,1);
   set step1;
run;

[AMSAS]

Not sure if this is what you are looking for. The code below goes out and grabs all the files in a directory and creates variable from the file names. I included a link to the External File Functions.

/*

External File Functions
https://go.documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=allprodsle&docsetTarget=syntaxByCategory-function.htm&locale=en#n1xjxmfjlyclzjn1hyatp8m76hr7

*/

filename myDir "\Some\folder\some\where" ;

data want ;
 	/* get a directory handle for the directory */
	dirRC=dopen("myDir") ;
	put dirRC= ;
	/* get the number of files in the directory */
	dNum=dnum(dirRC) ;
	put dNum= ;
	/* loop through the files */
	do i=1 to dnum ;
		/* get the filename */
		filename=dread(dirRC,i) ;
		put filename= ;
		/* now do whatever you want with the filename */
		var1=substr(filename,1,4) ;
		output ;
	end ;
run ;