Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Contributor
Posts: 65
Accepted Solution

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

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?


Accepted Solutions
Solution
‎10-21-2016 02:30 PM
PROC Star
Posts: 1,231

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

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.

View solution in original post


All Replies
Super User
Posts: 17,831

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

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.

Contributor
Posts: 65

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

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

Solution
‎10-21-2016 02:30 PM
PROC Star
Posts: 1,231

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

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.

Contributor
Posts: 65

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

Thank you! This worked perfectly!
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 217 views
  • 0 likes
  • 3 in conversation