topic Re: Adding a variable based on the filename and exporting the file as its original type in SAS Programming

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

mmhxc5 — Fri, 01 Feb 2019 18:56:11 GMT

Hi,

I would like to post my question again, because the last time I did not get a satisfactry answer.

I would like to add variable YEAR from 1992 to 2017 based on the filename for each file available in comma delimited .txt type. The file names are ID1992, ID1993, ..., ID2017,and export it back as its original file, that is .txt.

Each file has 137 variables and more than 50,000 rows of data - character and numeric. I don't want to interfere within the files, but just want to add another variable YEAR for each row of each file.

I appreciate your time and help.

Thanks,

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

Reeza — Fri, 01 Feb 2019 19:54:08 GMT

Show the code that you can use to successfully read a single file. Then that code can be modified to accomplish this. After reviewing your previous question, I believe this would be enough information to solve your problem.

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

ballardw — Fri, 01 Feb 2019 20:45:37 GMT

I strongly suggest backing up you text files before you start such things. Any error on your part could destroy your original file.

Will you need to insert a column header?

data _null_;
   infile "<path>\id1922.txt" lrecl=10000;/* needs to be at least as long as longest input line*/
   file "<path>\newid1922.txt";
   input;
   if _n_=1 then outline= catx(',',_infile_,'Year');
   else outline=catx(',',_infile_,'1992');
   put outline;
run;

Replace path with your location. If you write to a different folder then you could use the same file name.

This provides a header row. If you don't need one remove the IF - else only have the last outline.

If this is sufficient then steps can be made to automate this.

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

mmhxc5 — Fri, 01 Feb 2019 21:14:22 GMT

@Reezathank you!

Here is my code that is doing the job one by one. I want this code to be automated to all at once.

proc import datafile='E:\Idaho\ID2017.txt'
dbms=dlm out=work.test;
delimiter =",";
getnames=yes;
guessingrows=100000;
run;
data Work.ID2017ay;
set test;
Inspection_year=2017;
run;
proc export data=Work.ID2017ay replace
outfile = 'E:\Ad_Year\ID2017ay'
dbms=dlm;
delimiter=',';
run;

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

ballardw — Fri, 01 Feb 2019 21:26:48 GMT

@mmhxc5 wrote:

@Reezathank you!

Here is my code that is doing the job one by one. I want this code to be automated to all at once.

Did you try my example?

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

Reeza — Fri, 01 Feb 2019 21:58:16 GMT

If you wanted to automate that, you could turn it into a macro and then call it for each year.

Here's a tutorial for that.

https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

@ballardw solution will work as well, but I'm not a huge fan of running code I don't understand.

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

VDD — Fri, 01 Feb 2019 23:55:28 GMT

%macro runner(yy=);
	proc import datafile="E:\Idaho\ID&yy..txt"
		dbms=dlm out=work.test;
		delimiter =",";
		getnames=yes;
		guessingrows=100000;
	run;
	data Work.ID&yy.ay;
		set test;
		Inspection_year=&yy.;
	run;
	proc export data=Work.ID&yy.ay replace
		outfile = "E:\Ad_Year\ID&yy.ay"
/* or do you want the output file to be Text
	use this
		Outfile = "E:\Ad_Year\ID&yy.ay.txt"
*/
		dbms=dlm;
		delimiter=',';
	run;
proc datasets lib=work;
delete test;
quit;
%mend runner;
%runner(yy=1992);
%runner(yy=1993);
.
.
%runner(yy=2017);

Thank you @Reeza for the eagle eye added a delete.

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

Reeza — Fri, 01 Feb 2019 23:09:07 GMT

@VDD, should probably drop the test data set at the end of the loop. Otherwise if the import files, it'll use the previous years data.

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

Tom — Fri, 01 Feb 2019 23:44:36 GMT

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?

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

VDD — Fri, 01 Feb 2019 23:51:30 GMT

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

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

Tom — Sat, 02 Feb 2019 00:05:28 GMT

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

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

ballardw — Sat, 02 Feb 2019 00:49:21 GMT

@Reeza wrote:

If you wanted to automate that, you could turn it into a macro and then call it for each year.

Here's a tutorial for that.

https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

@ballardw solution will work as well, but I'm not a huge fan of running code I don't understand.

@Reeza

My example is basically a buffer manipulation. The Input places the entire line of data into the automatic variable _infile_ then depending on whether we need a header row or not appends a comma, since the OP says the file is CSV, and either the text YEAR for a header or the value of a year.

The FILE statement documentation even has hints on this:

Updating an External File in Place

You can use the FILE statement with the INFILE and PUT statements to update an external file 
in place, updating either an entire record or only selected fields within a record. 
Follow these guidelines: 
 
Always place the INFILE statement first. 

Specify the same fileref or physical filename in the INFILE and FILE statements. 
 
Use options that are common to both the INFILE and FILE statements 
in the INFILE statement. (Any such options that are used in the FILE statement are ignored.) 
 
Use the SHAREBUFFERS option in the INFILE statement to allow the INFILE and FILE statements 
to use the same buffer, which saves CPU time and enables you to update individual fields 
instead of entire records.

I could have used the _file_ variable as well instead of the variable.

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

Tom — Sat, 02 Feb 2019 00:52:03 GMT

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

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

Reeza — Sat, 02 Feb 2019 17:50:20 GMT

@ballardw I understand your code, I wasn't sure the OP would, so s/he shouldn't run it :).

Rationale for this type of 'rule' is that if you don't understand it enough you cannot make changes or understand what's happening when something goes wrong. Edge cases can also slip through if you're not checking your cases correctly.

Your solution is the one I would recommend as being the most correct. However, the macro approach will work as well.

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

mmhxc5 — Sun, 03 Feb 2019 04:46:05 GMT

@VDD, thank you for your nice code. I run the code, it works well, but it adds only the Inspection_Year for 2017. Other years are blank. Could you please look at the code and see if there is any error? I really appreciate it. Thank you!

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

Reeza — Sun, 03 Feb 2019 05:47:43 GMT

Did you add in the macro call for the other years?

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

Patrick — Sun, 03 Feb 2019 06:12:28 GMT

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

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

mmhxc5 — Sun, 03 Feb 2019 16:46:41 GMT

@Reeza, Yes, I added the macros for the other years. Here is the complete code I run, it works neatly, but only adds the Inspection_Year for 2017 and the other years are left blank.

%macro runner(yy=);
	proc import datafile="E:\Desktop\Idaho\ID&yy..txt"
		dbms=dlm out=work.test;
		delimiter =",";
		getnames=yes;
		guessingrows=1000000;
	run;
	data Work.ID&yy.ay;
		set test;
		Inspection_year=&yy.;
	run;
	proc export data=Work.ID&yy.ay replace
		outfile = "E:\Desktop\Idaho\Ad_ear\ID&yy.txt"
		dbms=dlm;
		delimiter=',';
	run;proc datasets lib=work;delete test;quit;
%mend runner;
%runner(yy=1992);
%runner(yy=1993);
%runner(yy=1994);
%runner(yy=1995);
%runner(yy=1996);
%runner(yy=1997);
%runner(yy=1998);
%runner(yy=1999);
%runner(yy=2000);
%runner(yy=2001);
%runner(yy=2002);
%runner(yy=2003);
%runner(yy=2004);
%runner(yy=2005);
%runner(yy=2006);
%runner(yy=2007);
%runner(yy=2008);
%runner(yy=2009);
%runner(yy=2010);
%runner(yy=2011);
%runner(yy=2012);
%runner(yy=2013);
%runner(yy=2014);
%runner(yy=2015);
%runner(yy=2016);
%runner(yy=2017);

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

mmhxc5 — Sun, 03 Feb 2019 16:44:03 GMT

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

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

Reeza — Mon, 04 Feb 2019 01:18:51 GMT

Add:

options mprint symbolgen;

Run it for two files and post the log please.