topic Create a Macro List in SAS Programming

Create a Macro List

ashdrews19 — Thu, 14 Jan 2016 17:49:18 GMT

Hello there,

I am new to SAS and am having difficulties completing an easy macro. I know how to run it in Stata (with the for var statement) but I seem to struggle trying to translate it to SAS.

I am working on a project where we have 50 datasets from all 50 states. We are given SAS files that automatically clean and compilate the flat-files for each state. So, I am trying to run a macro that cycles through all 50 states and opens up the SAS file associated with that state, runs it, and gives us a final dataset for that state.

I managed to create a working macro that allows me to call the other datasets:

%Macro statecycle(abbr);
%include "C:\Users\ashland.PMACS\Desktop\Testing\&abbr.\SAS_test&abbr..sas" ;
%Mend statecycle;

However, I am really struggling figuring out how to run a macro list that changes abbr to the a state. I tried everything from a let statement to importing a dataset with only the state abbreviations and trying to run an entire variable.Everything I've tried doesn't work, or maybe I'm on the right track I just have no idea what the codes are.

I know that in stata there is a "for var " statement where I can give it a list of names and associate that list with a letter (e.g. `x') and whenever I call on `x' in my code it cycles through all the names I gave it. I feel like there is a simple solution for SAS, I just haven't been able to figure it out.

I am thankful for any help or advice.

Re: Create a Macro List

Reeza — Thu, 14 Jan 2016 17:55:19 GMT

I think you're looking for a format to apply it to map the state abbrev to state.

Also, you may want to take a look into call execute for automating procedures from a data driven table.

Re: Create a Macro List

Astounding — Thu, 14 Jan 2016 17:59:48 GMT

This won't give you all the answers, but it will help you start thinking in the right direction:

http://blogs.sas.com/content/publishing/2015/01/30/sas-authors-tip-getting-the-macro-language-to-perform-a-do-loop-over-a-list-of-values/

Re: Create a Macro List

Reeza — Thu, 14 Jan 2016 18:09:55 GMT

Here's an example

data state_fmt;
set mapssas.us2;
start=statecode;
label=statename;
fmtname='$abbr2state';
type='C';
run;

proc format cntlin=state_fmt;
run;

%put %sysfunc(putc(AL, $abbr2state.));

Re: Create a Macro List

ashdrews19 — Thu, 14 Jan 2016 20:13:02 GMT

Hi Reeza,

I tried to run the code, and I am a little confused as to what it would be doing. Is the format making the state names into abbreviations? Because my data is already in the abbreviated form. I'm just trying to figure out how to make a list of all 50 abbreviations and call all 50 variables when I run the macro I already have.

I've been trying the code as such:

%Let statename = AZ PA ... WA

%Macro statecycle(abbr);
%include "C:\Users\ashland.PMACS\Desktop\Testing\&abbr.\SAS_test&abbr..sas" ;
%Mend statecycle;

%statecycle (&statename);

However, a let statement doesn't allow me to do multiple items in a row.

Re: Create a Macro List

Reeza — Thu, 14 Jan 2016 20:33:52 GMT

Then create a list and loop through it.

Create a list:

proc sql;
select statecode into :state_list separated by " "
from mapssas.us2;
quit;

%put &state_list;

The blog link above illustrates looping through a list, though I really, really, really think you should look into call execute.

Loop through macro variables - my example

https://gist.github.com/statgeek/9603186

Call execute - look at the example.

proc sql;
create table state_list as
select statecode from mapssas.us2;
quit;

*Add call execute to your macro here;

http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/viewer.htm#a000543697.htm