BookmarkSubscribeRSS Feed
ashdrews19
Calcite | Level 5

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. 

 

5 REPLIES 5
Reeza
Super User

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.

Astounding
PROC Star

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-per...

 

 

Reeza
Super User

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.));
ashdrews19
Calcite | Level 5

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. 

 

 

Reeza
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 786 views
  • 2 likes
  • 3 in conversation