topic Re: Macros and Arrays - Is there an easier way to loop through a list in SAS Procedures

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

einstein — Thu, 20 Oct 2016 20:38:51 GMT

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?

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

Reeza — Thu, 20 Oct 2016 20:45:31 GMT

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.

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

einstein — Thu, 20 Oct 2016 20:52:07 GMT

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

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

Quentin — Thu, 20 Oct 2016 21:02:07 GMT

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.

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

einstein — Fri, 21 Oct 2016 18:31:32 GMT

Thank you! This worked perfectly!