Macros and Arrays - Is there an easier way to loop through a list

[einstein]

I have a macro that reads through a list of states and years, however, I'm wondering if there's a more efficient way to read through the states instead of only using a macro.

Here is my code:

%macro test (st=,year=);

proc freq data=data.out_&st._&year.;
tables place;
run;
%mend test;
%test (st=NY, year=2011);
%test (st=NY, year=2012);
%test (st=NY, year=2013);
%test (st=VA, year=2011);

etc.

Is there an easier, more consise way to run through the years without having to have three lines for NY for each year?  Should I use an array?

[Reeza]

Why is your data split like that in the first place?

Perhaps combining them from the start would help.

Unless your datasets are in the millions of rows each or possibly tens, there isn't a need to separate the data like that.

[einstein]

Yep, these datasets have millions of observations, which is why they're split by state and year.

[Quentin · Accepted solution]

Is the list of years the same for every state?  If so, then it should be straight forward to add some %DO loops in your macro.

So instead of calling it once for each freq, you could call it like:

%test (st=NY VA RI, startyear=2011, stopyear=2013)

That doesn't change the execution efficiency, but it does make it easier to generate the set of PROC FREQ steps without writing each macro call yourself.

Another option would be to have a dataset that has one record per freq table you want, with variables State and Year.  Then you can use that dataset to drive your macro.  For example, using CALL EXECUTE to read a record from the dataset and generate a macro call.

Even if there are millions of records, I'd still consider putting them all together and indexing the dataset.

[einstein]

Thank you! This worked perfectly!