How to add a variable column based on the filename

[mmhxc5]

Hi,

I would like import several comma delimited .txt files at once with names  ID1992, ID1993, ..., ID2017, add variable YEAR from 1992, to 2017 based on the file name for each file and export it back as its original file, that is .txt.

I appreciate your time and help.

Thanks,

[Tom]

Use the FILENAME= option on the INFILE statement to know what file you are reading.

%let path=/folder/with/the/files;
data want ;
  length filename $200 ;
  input "&path/ID*.txt" dsd filename=filename truncover ;
  input @ ;
  year = input(substr(scan(filename,-2,'./\'),3),4.);
  input var1 var2 ....;
run;

[mmhxc5]

@Tom, Thank you for your reply.

This is what I wrote.

%Let path =E:\Desktop\Idaho;
data want;
	length filename $200;
	input "&path/ID*.txt" dsd filename=filename truncover;
input @;
year = input(substr(scan(filename,-2'./\'),3,4.);
input Year;
run;

and the following is the error I am getting.

[Tom]

The statement with the path and truncover and other options is INFILE not INPUT.

In the assignment statement for YEAR you are missing a comma between the last two arguments of the SCAN() function.

You don't want to INPUT the variable YEAR since you just gave it a value on the line above.  You want to input the variable(s) that are actually in your csv files.

Also if your CSV files each have a header rows then you will need to add this before the last INPUT statement so that you can skip the header.

if filename ne lag(filename) then delete;

[LinusH]

Actually, you had INPUT in your example @Tom 😉

The other error  is due to a missing comma after -2.

[mmhxc5]

@Tom, Thanks for correcting me. I got the code a little further, but still I get another error.

Here is what I wrote.

%Let path =E:\Desktop\Idaho;
data want;
	length filename $200;
	infile "&path/ID*.txt" dsd filename=filename truncover;
infile @;
year = input(substr(scan(filename,-2,'./\'),3),4.);
if filename ne lag(filename) then delete;
input Inspection_Year;                
run;

and the following is the error I get.

[Tom]

You changed the first INPUT statement (with the trailing @ )  that was reading and holding the next line of data so that the FILENAME variable would get set to an invalid INFILE statement.   Convert it back to in INPUT statement.

What data is in the files?  Do they really only have one number per line?

[mmhxc5]

@Tom , Thanks for your reply. I revised the code and added INPUT statement (with the trailing @), the code worked without any error. As I said in my first post, I want to add a column YEAR within each file based on its filename and export it as it is. Based on your code I get a data set combined as SAS dataset with variables I select from the .txt files. I don't want to interfere with the variables inside the files, I just want to add the column YEAR for each file and export it as .txt.

Each file has 137 variables and more than 50,000 rows of data - character and numeric. I really appreciate your help.

[Tom]

If you want to copy the text then use a DATA _NULL_ add a FILE statement to point to the NEW file you want to create. Then use the _INFILE_ automatic variable to copy the line.  So after you have figured out the year instead of trying the read the data from the line your code looks something like this:

file "newfilename";
put year _infile_;

You might need to set a LRECL= on the FILE statement if the lines are longer than the default 32K bytes.

You might want to put a delimiter character after the YEAR value and before the rest of the line.

You might want to eliminate the space that SAS will normally put after the value of YEAR.

You might be able to do both by adding the DSD and DLM= option to the FILE statement. Test it and see.  But you could just do it in the PUT statement.

For example if you wanted to add a comma right after the value of year.

put year +(-1) ',' _infile_;