Need a macro to apply syntax to range of years and variety of states

[Wolverine]

We're testing all sorts of healthcare quality measures.  Our data currently includes data on all 50 states from 2005-2009.  Eventually we'll get 2010 and possibly 2011 as well.  Each state and each year have their own separate files.  Currently I'm running syntax on FL, IL, LA, MI, SC, and TX.  Our next round will include CA and CO as well.  My process up to this point has to be write the syntax I need for TX for 2005.  Then I do a find and replace to create another version for 2006.  The measures are restricted to kids of certain ages, so I manually have to change the birthdates for each year as well.  After all the years are done for TX, I do a find and replace to change the state. This process is repeated until all the states and all the years are covered.  The filename and folder structure are consistent so that only the state and year vary across files and folders.

So what I'd like is a macro that would allow me to input a list of states, a range of years, and the required birthdates for 2005.  Then the macro would take the appropriate syntax I've written for the measure, and automatically apply it for each state and each year (including corrected birthdates).  The macro would also adjust the titles for the output tables with the correct year and state.

I also forgot to mention that some of the measures require a 2-year enrollment period to be sure that the kid had Medicaid coverage in the year prior to the measurement year.  In other words, we have to combine data from 2005 and 2006 to do a measurement on 2006.  And in this situation, we can't test 2005 as the measurement year.  And even though we using the 2005 data, the birthdates would be for 2006 because that is the measurement year.  But other measures just use a 1 year enrollment period, so we only need data from the measurement year itself.  So ideally, the macro could ask what the enrollment period is and adjust the years accordingly.

[snoopy369]

Is your syntax the same for each state/year/etc.?  So you've got something like

proc means data=TX_DATA_2005;

var OUTCOME1 OUTCOME2;

outout out=TX_OUTPUT_2005 mean=;

run;

and you want to alter "TX" and "2005" ?

You may be able to do this without a macro, depending on your analysis.  Many analyses can be done with BY or CLASS to handle the the states.

For example, if you have one dataset MYDATA, and it has STATE and YEAR as variable:

proc means data=MYDATA;

class state year;

types state*year;

var OUTCOME1 OUTCOME2;

output out=MYDATAOUT mean=;

run;

That would work much more easily than a macro, and BY STATE YEAR; would work as well.

If that doesn't work for some reason (and I strongly encourage trying that first, it's much more maintainable), this is roughly how you'd do things:

%macro myAnalysis(State=,year=);

proc means data=&state._DATA_&year.;

var OUTCOME1 OUTCOME2;

outout out=&state._OUTPUT_&year. mean=;

title "Analysis of &state for &year.";

run;

%mend myAnalysis;

proc sql;

select distinct cats('%myAnalysis(state=',state,',year=',year,')') into :runlist separated by ' '

from styrdataset;

quit;

&runlist.

That assumes that STYRDATASET has the various possible state/years in it in some fashion (this might be a source dataset, or an artificially created one, or even an excel file you import that someone who wants an analysis run for a state/year set fills out).  &runlist. calls the macro %myAnalysis.

[Reeza]

http://www.ats.ucla.edu/stat/sas/library/nesug99/bt066.pdf

This isn't a hard job if you have the code written already, just need to learn where to define the parameters.

The paper above will get you started and finished probably.

Also, I'm actually unsure what you're question is.  You've stated the need for a macro, so are you:

1. Looking to hire a programmer to convert code to macro

2. Looking for references

3. Looking for instructions

4. Looking for a helpful person to read your mind and magically understand your data structure, code and create a macro and post it here

5. Looking for how to convert your current code (you would need to post it) to a macro. Though I highly suggest reading above paper and trying yourself first.

[Astounding]

Regardless of who does the coding, make sure that the macro works for Oregon and Nebraska.  Their abbreviations (OR and NE) are also significant words in macro language and, depending on how they are used in the macro, can require special handling.

[Wolverine]

Sorry I haven't been more prompt in replying -- we had another project come up that took precedence.  And now w/ the holiday break coming up, I probably won't get back to this till January.

snoopy369 wrote:

Is your syntax the same for each state/year/etc.?  So you've got something like

proc means data=TX_DATA_2005;
var OUTCOME1 OUTCOME2;
outout out=TX_OUTPUT_2005 mean=;
run;

and you want to alter "TX" and "2005" ?

You may be able to do this without a macro, depending on your analysis.  Many analyses can be done with BY or CLASS to handle the the states.

For example, if you have one dataset MYDATA, and it has STATE and YEAR as variable:

Yeah that's the basic idea (though the syntax is a lot more complicated).  The problem with starting from the original source file that has all 50 states is that the files are massive (about 320GB).  It takes about 3hrs just to extract the data for 1 state for 1 year.  That's why I have separate files for each state/year combination.  I could take just the 6 states of interest and combine them into a smaller source file, but I still think that would result in much slower processing times.  Besides, the states of interest will change depending on the disease we're investigating.

Also, I'm actually unsure what you're question is.  You've stated the need for a macro, so are you:
1. Looking to hire a programmer to convert code to macro
2. Looking for references
3. Looking for instructions
4. Looking for a helpful person to read your mind and magically understand your data structure, code and create a macro and post it here 
5. Looking for how to convert your current code (you would need to post it) to a macro. Though I highly suggest reading above paper and trying yourself first.

Well #4 would be great, actually :smileysilly:  But a combination of 2, 3, and maybe a bit of 4 is more realistic!  I took a seminar on SAS macros, but it was several years ago and I never actually wrote any macros.  So I have very limited knowledge on the subject.  The paper u posted looks like it will be helpful, so maybe the best thing to do at this point is try to write as much of the macro as I can based on the paper and what snoopy369 posted, and then bring that back here for advice on how to tweak it.

Regardless of who does the coding, make sure that the macro works for Oregon and Nebraska.  Their abbreviations (OR and NE) are also significant words in macro language and, depending on how they are used in the macro, can require special handling.

Good to know.  I'm not sure we'll be using those states at any point, but I'll keep it in mind!