topic Re: How to add a variable column based on the filename in SAS Programming

How to add a variable column based on the filename

mmhxc5 — Sun, 27 Jan 2019 04:44:59 GMT

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,

Re: How to add a variable column based on the filename

Tom — Sun, 27 Jan 2019 04:59:28 GMT

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;

Re: How to add a variable column based on the filename

mmhxc5 — Sun, 27 Jan 2019 15:32:52 GMT

@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.

Re: How to add a variable column based on the filename

Tom — Sun, 27 Jan 2019 16:59:35 GMT

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;

Re: How to add a variable column based on the filename

LinusH — Sun, 27 Jan 2019 17:55:27 GMT

Actually, you had INPUT in your example @Tom 😉

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

Re: How to add a variable column based on the filename

mmhxc5 — Mon, 28 Jan 2019 03:19:04 GMT

@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.

Re: How to add a variable column based on the filename

Tom — Mon, 28 Jan 2019 05:31:50 GMT

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?

Re: How to add a variable column based on the filename

mmhxc5 — Wed, 30 Jan 2019 15:50:35 GMT

@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.

Re: How to add a variable column based on the filename

Tom — Wed, 30 Jan 2019 16:26:15 GMT

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_;