topic Re: How to use macro for infile multiple txt? in SAS Procedures

How to use macro for infile multiple txt?

dupp99 — Mon, 02 Dec 2019 23:34:03 GMT

I want the macro to have one parameter which is the path of the text files.

This is what I have now. I have 6 txt with name= NY2012.txt NY2013.txt NY2014.txt.....etc.

%macro data ;

%let list= 2012 2013 2014 2015 2016 2017;


data allData;
delete;  (what should I put here?)
run;


%do i=1 %to 6;
%let currItem = %scan(&list, &i);

filename f&list "/folders/myfolders/NY&list.txt"; (should this be here?)
data currentData;
x = &currItem;

infile f&list  truncover;
input value $ 1-20;
retain year;
if _n_=1 then year=year_1;

run;

* Keeping adding things to the cumulative data set;
data allData;
set allData currentData;
run;
%end;

%mend data;

%data;

Then I should end up with a dataset for each year and one large dataset that included all the years. How should I fix this? Thank you.

Re: How to use macro for infile multiple txt?

data_null__ — Tue, 03 Dec 2019 00:09:04 GMT

Why not use a wildcard * in the filename. Then use the EOV and FILENAME INFILE statement options to know the data source.

Re: How to use macro for infile multiple txt?

PaigeMiller — Tue, 03 Dec 2019 00:10:33 GMT

filename f&list "/folders/myfolders/NY&list.txt";

replace the above with

filename flist "/folders/myfolders/NY*.txt";

No macro needed. This command reads all files NY*.txt at once.

Also, if the variable names are the same (and variable types are the same) in all the NY*.txt files, use PROC APPEND rather than a SET command in a DATA step to append.

Re: How to use macro for infile multiple txt?

Tom — Tue, 03 Dec 2019 00:56:51 GMT

To delete a dataset use PROC DELETE.

proc delete data=alldata; run;

You can use the FILEVAR= option on the INFILE statement to tell SAS to use the value of the named varaible as the name of the file to read.

If your list of values is separated by commas you can use them in a DO statement.

do year=2012,2013,2014,2015,2016,2017;

But it also looks like list is just all years from 2012 to 2017.

do year=2012 to 2017;

So putting it together here is how you could do it without any macro logic at all. But perhaps just a single macro function call to change the space delimited list into a comma delimited list.

%let list= 2012 2013 2014 2015 2016 2017;
%let listcomma=%sysfunc(translate(&list,%str(,),%str( )));

data alldata ;
  do year=&listcomma ;
     length fname $200 ;
     fname = cats("/folders/myfolders/NY",year,".txt");
     infile txt filevar=fname end=eof truncover;
     do while(not eof);
       input value $20. ;
       output;
     end;
  end;
run;

Re: How to use macro for infile multiple txt?

dupp99 — Tue, 03 Dec 2019 04:46:55 GMT

I need to do them using the macro. I think what I wrote is almost right, use needs some small changes. Thank you!

Re: How to use macro for infile multiple txt?

dupp99 — Tue, 03 Dec 2019 05:31:22 GMT

I need to do them using the macro. How could I do them using macro?

Re: How to use macro for infile multiple txt?

dupp99 — Tue, 03 Dec 2019 05:32:08 GMT

I need to do them using the macro. How could I do them using macro? or make more changes based on the codes I wrote. Thank you!

Re: How to use macro for infile multiple txt?

Kurt_Bremser — Tue, 03 Dec 2019 06:44:09 GMT

NO MACRO IS NEEDED.

One only uses macro coding if it is absolutely necessary; in your case it isn't. @Tom has shown a complete solution in one data step.

Re: How to use macro for infile multiple txt?

PaigeMiller — Tue, 03 Dec 2019 11:33:33 GMT

@dupp99 wrote:

I need to do them using the macro. How could I do them using macro?

Well, no, as demonstrated above, you do not need a macro. It's just extra complications (as you have found) for no benefit.

Re: How to use macro for infile multiple txt?

Tom — Tue, 03 Dec 2019 11:34:18 GMT

Before you can use a macro to generate code you have to know what code you need to generate. So work on getting code to work for one file. Then you could think about building a macro (if one is needed).