Adding a variable based on the filename and exporting the file as its original type

[Patrick]

@mmhxc5 

Others already provided solutions so not adding to this. I'd like to ask the question though why you would want to add this YEAR column to the text file in first place as you've got this information already in the filename itself. It's not hard to extract the year from the filename when reading the data into SAS (code below).

filename in 'c:\temp\id*.txt';
data sample;
  length thisFile $300 year 8;
  infile in lrecl=255 truncover dlm=',' filename=thisFile;
  input; /* here your variable mapping reading the data */
  year=input(scan(thisFile,-2,'id.','ti'),best32.);
run;

[mmhxc5]

@Patrick, I need the Year column in each file, whereas it is only provided on the filename. Now I use the filename to add the Year column inside each file.

[Reeza]

Add:

options mprint symbolgen;

Run it for two files and post the log please.

[Tom]

Do NOT write the files back to the same name.  What if something goes wrong.

Are you going to write the files back to the same directory?  Or a different directory.

Do the files have header rows?

[VDD]

@mmhxc5 has an in directory Idaho and an out directory Ad_year

[Tom]

Do want to still have multiple files? Or can you just make one file?

[Tom]

It sounds like you have multiple files like this:

>more ID*.txt | more
::::::::::::::
ID1992.txt
::::::::::::::
X,Y
1,2
::::::::::::::
ID1993.txt
::::::::::::::
X,Y
3,4

And you want to add a new column to each row.

So just read in each row and write them back out.

%let inpath=~/test/example;
%let outpath=&inpath/new ;

data _null_;
  length year infilename outfilename $200;
  infile "&inpath/ID*.txt" filename=infilename;
  input ;
  outfilename = catx('/',"&outpath",scan(infilename,-1,'/\'));
  year = substr(scan(infilename,-2,'/.\'),3);
  file out filevar=outfilename ;
  if infilename ne lag(infilename) then put 'YEAR,' @;
  else put year +(-1) ',' @;
  put _infile_;
run;

Results:

->more ID*.txt | more
::::::::::::::
ID1992.txt
::::::::::::::
YEAR,X,Y
1992,1,2
::::::::::::::
ID1993.txt
::::::::::::::
YEAR,X,Y
1993,3,4