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.
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.
This won't give you all the answers, but it will help you start thinking in the right direction:
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.));
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.