Loop through dataset named with month_year

[newboy1218]

Hi, I have datasets named with AA_MARCH_2019, AA_APRIL_2019, ..., AA_MAY_2021, and I want to create a loop function to execute the same process in each monthly file. How can I create a do loop to loop through these month_year files? Most of the SAS date format includes the date field, but here I just want the full month name and the year.

Thanks.

[Kurt_Bremser]

Do not name datasets like this. Simply DON'T.

Name them

AA_201903
AA_201904

and so on.

Then you can use SAS dates and PUT with a YYMMN6. format. On top of that, the datasets will sort correctly on their own.

Big hint: stupid data design forces stupid code.

[newboy1218]

Hi,

Yeah I understand. However, those files are created by our company data team. Now I just need to access them unfortunately. =(

[PaigeMiller · Accepted solution]

%macro dothis(start,end);
    %let start=%sysevalf("&start"d);
    %let end=%sysevalf("&end"d);
    %let month=&start; 
    %let incr=0;
    %do %while(&month<=&end);
        %let filename=%sysfunc(putn(&month,monname3.))_%sysfunc(year(&month));
        /* Now that you have the filename, you can put code here that uses the file name */
        %let incr=%eval(&incr+1); %put &=incr;
        %let month=%sysfunc(intnx(month,&start,&incr,b)); 
    %end;
%mend;
%dothis(01MAR2019,01MAY2021)

[ballardw]

---

@newboy1218 wrote:

Hi,

Yeah I understand. However, those files are created by our company data team. Now I just need to access them unfortunately. =(

---

I would have a discussion with the "data team" about the , let us say, suboptimal, naming pattern.

Explain that a YYYYMM, ie 201912 1) sorts names properly at the operating system level as well as SAS library displays and 2) allows use of data set Lists in SAS. Depending on what process is currently naming them it may be easier to maintain as well as use.

[Kurt_Bremser]

Send this "data team" back to college, so they can finish Computer Science 101.

It really hurts to see so-called professionals commit such egregious bloody-beginner mistakes.

[Reeza]

It's also a trivial exercise to rename them via alias or views so this isn't a hill I'd die on by any means.

[ballardw]

It might depend on what you are doing but instead of "looping through files" you could combine them, add a file identifier if needed and then use BY group processing for the stuff needed using that identifier.

Make that identifier something that sorts in proper order.

Note that the INDSNAME option on a SET statement allows you to create a temporary variable with the name of the data set contributing the current record that you could parse as need.

[PaigeMiller]

---

@ballardw wrote:

It might depend on what you are doing but instead of "looping through files" you could combine them, add a file identifier if needed and then use BY group processing for the stuff needed using that identifier.

Make that identifier something that sorts in proper order.

Note that the INDSNAME option on a SET statement allows you to create a temporary variable with the name of the data set contributing the current record that you could parse as need.

---

If possible with these data sets, that would be even better than using a macro.

You could combine all data sets into one via 

data all;
     set aa_: indsname=indsname;
     dsname=indsname;

and now you have everything you need to work with BY statements rather than a macro loop.

[newboy1218]

Hi,

I see. So in the set statement, I would be combining all the datasets that has the name start with 'aa_' right? But what if I just want to combine a certain range of files? For example, in the library I have aa_january_2018 to aa_may_2021, and now I only need to use the files from aa_january_2019 to aa_may_2021?

And for the variable indsname, it would be the name of the aa_ file right?

Thanks.

[Reeza]

SAS won't recognize the pattern in the dates if they're listed with the month name. If the pattern had the month numbers as suggested by Kurt earlier, then you could use a shortcut list to list the datasets.

You can query sashelp.vtable to create a dynamically built list within your parameters.