concate month to file and read file

[fsuzhang]

I tried to read in 500 files, named as red_OCT10,...

%let From_Date = JAN07;
%let new_input=%sysfunc(inputn(&From_Date., MONYY5.));
%put &new_input.;

using the following code

data test3;
do k=0 to 500;

%let y=%sysfunc(putn(%sysfunc(intnx(month, &new_input., k , 'e')), MONYY5.));

set rec_&y.;
output;
end;
run;

system can not recognize the looped k as number

'Argument 3 to function INTNX referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number.'

How to solve this system function issues, thanks.

[Reeza]

1. Do all files have the same structure and variables?
2. Are you reading each data set into it's own data set or combining them all?
3. Do you have working code for one file?
4. Have you tried a wild card approach or getting a master list of datafiles to import?

If all files are the same structure/variables you can read all in a single step. 

If you're reading them to a different data set, it can still be automated. A loop isn't the best way to do this, but there are many ways to do this efficiently. 

---

@fsuzhang wrote:

I tried to read in 500 files, named as red_OCT10,...

%let From_Date = JAN07;
%let new_input=%sysfunc(inputn(&From_Date., MONYY5.));
%put &new_input.;

using the following code

data test3;
do k=0 to 500;

%let y=%sysfunc(putn(%sysfunc(intnx(month, &new_input., k , 'e')), MONYY5.));

set rec_&y.;
output;
end;
run;

system can not recognize the looped k as number

'Argument 3 to function INTNX referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number.'

How to solve this system function issues, thanks.

---

[fsuzhang]

all the files have same structure and variable,

I need to combine them into one big file, 

I didn't try the wild card like *, the actual input file names are 'REC_MMMYY_INIT', I tried to concate the macro variable in middle.

[Tom]

---

@fsuzhang wrote:

all the files have same structure and variable,

I need to combine them into one big file, 

I didn't try the wild card like *, the actual input file names are 'REC_MMMYY_INIT', I tried to concate the macro variable in middle.

---

%macro dsnames(from,number);
%local i ;
%do i=0 %to &number;
   rec_%sysfunc(intnx(month,&from,&i),monyy5.)_init
%end;
%mend dsnames;

data want;
  set %dsnames(from='01JAN2007'd,number=500) ;
run;

[Reeza]

Please show us the code that works to read a single file. 

Once you have that you can replace the date portion with an * and SAS will read all the files at once into a single file. 

But first you need to have working code to read one file. 

---

@fsuzhang wrote:

all the files have same structure and variable,

I need to combine them into one big file, 

I didn't try the wild card like *, the actual input file names are 'REC_MMMYY_INIT', I tried to concate the macro variable in middle.

---

[andreas_lds]

---

@fsuzhang wrote:

all the files have same structure and variable,

I need to combine them into one big file, 

I didn't try the wild card like *, the actual input file names are 'REC_MMMYY_INIT', I tried to concate the macro variable in middle.

---

Then the infile-statement with wildcard should be able to read all files in one step:

infile "path-to-files/REC_*_INIT" /* maybe more options depending on file-type */;

When talking about "files", you mean text-files, not any binary-stuff like xlsx or xls?

[Tom]

Question as stated does not make any sense.

Are you trying to combine 501 datasets into one dataset? 

If so are you trying to do something like:

data test3;
   set recJAN07 recFEB07 recMAR07 .... 

Or did you have some other code in mind?  Perhaps you want to generate 501 data steps that each read in one existing dataset?  

Please explain what you are trying to do.  If you think that requires you to generate code then show an example of the code you want to generate and explain what parts vary between the multiple versions you need to generate.

[fsuzhang]

I tried to loop the set step

data test3;

set REC_OCT10_INIT; set REC_OCT11_INIT;.....

the datasets have the similar structure, I attempt to use macro variable to replace 'OCT10',  'OCT11'.... which are by month.

[Reeza]

Are your data sets SAS data sets? If so, you can list them all at once. If they all have the same prefix for example try this:

data test;
set REC: ;
run;

/*Only October*/
data october;
set rec_OCT: ;
run;

Here is a reference that illustrates how to refer to variables and datasets in a short cut list:
https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html

[fsuzhang]

Thanks a lot.

[PaigeMiller]

---

@fsuzhang :

Your looping is wrong, which is why you ABSOLUTELY MUST create example code without macros and without macro variables that works without errors and does what you want, for a small example. Correct code would be, for example with just two months

data test3;
    set rec_oct10 rec_nov10;
run;

A DATA step DO loop will not and cannot produce the above code, and a data step DO loop is not needed here.

Note, that looping inside a macro %DO loop, analogous to your code with a DATA step DO loop, with a SET statement inside the loop, is not going to get you the result you want, because this produces many SET statements, and correct code would use a single SET statement.

Based on the above code, a valid SAS macro would put all of the data set names in a single SET statement. So your macro %DO loop must be within a single SET statement.

So given the above, can you now turn the correct code into a valid macro?