topic Re: Loop through dataset named with month_year in SAS Programming

Loop through dataset named with month_year

newboy1218 — Mon, 21 Jun 2021 15:30:28 GMT

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.

Re: Loop through dataset named with month_year

Kurt_Bremser — Mon, 21 Jun 2021 15:36:28 GMT

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.

Re: Loop through dataset named with month_year

newboy1218 — Mon, 21 Jun 2021 15:39:19 GMT

Hi,

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

Re: Loop through dataset named with month_year

PaigeMiller — Mon, 21 Jun 2021 15:43:26 GMT

%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)

Re: Loop through dataset named with month_year

ballardw — Mon, 21 Jun 2021 15:49:02 GMT

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.

Re: Loop through dataset named with month_year

PaigeMiller — Mon, 21 Jun 2021 16:06:16 GMT

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

Re: Loop through dataset named with month_year

newboy1218 — Mon, 21 Jun 2021 16:11:33 GMT

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.

Re: Loop through dataset named with month_year

Reeza — Mon, 21 Jun 2021 16:28:01 GMT

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.

Re: Loop through dataset named with month_year

ballardw — Mon, 21 Jun 2021 19:29:16 GMT

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

Re: Loop through dataset named with month_year

Kurt_Bremser — Mon, 21 Jun 2021 21:31:15 GMT

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.

Re: Loop through dataset named with month_year

Reeza — Mon, 21 Jun 2021 22:41:38 GMT

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.