Loop Code - Annual Rate

[hlovejoy]

Hello,

I am trying to calculate annual rates of various diseases for various populations. I  already have code that uses multiple macros to grab the corresponding population data and everything else I need. What I don't know how to do (new SAS user), is make my code repeat itself for the next year, then the next year, etc, until a certain year. 

For example, the code below does everything I want for the year 2006. I want it to re-run for 2007, then 2008, etc, until 2016.

/*POPULATION FILE VARIABLES*/

%let pop_years = pop2006 ;
%let sum_years = pop2006;
%let pop_age = (AGEGROUP ne '');
%let n_years = 1;
%let geogdenom = FIPS_STATE IN (16, 41, 53);
%let pop_race_sex = (RACE = 1);

/*CASE FILE VARIABLES**/

%let dataset = Q ;
%let year = (year=2006);
%let age = (agegroup ne '') ;
%let geognum = FIPS_STATE_RES IN (16, 41, 53);
%let race = (race = 1) ;
%let sex = (sex ne '') ;
%let cause = QR = 1;

/*OUTPUT DESCRIPTORS*/

%let time = '2006';
%let measure = ' Q Deaths';
%let racesexage = 'Race1, Region Name, Both Sex, All Ages';

%Race1RATE;

This is an output example of what I currently get: 

Population	Year	Count	Pop	Rate	CI_Lower	CI_Upper	CI_LB	CI_UB	Ann_pop
Region, Both Sex, All Ages	2006	1063	11510305	8.98953	8.43434	9.54471	0.55518	0.55518	11510305

Ideal output would have additional lines for the years 2007, 2008, etc up to 2016.

Thank you so much!

[ChrisNZ]

Welcome the the SAS community!

With what you give us, we can only attempt to create several tables.

In order to create a single table and add rows, we'd need to know how the table is created.

[hlovejoy]

If you give me something that will create multiple tables, that is fine. Ideally looking for how to just loop the code I provided above, but adding 1 to the year each time until a specified end year.
Thank you!

[ChrisNZ · Accepted solution]

This might suit you:

/*POPULATION FILE VARIABLES*/

%macro loop;

%local year;
%do year=2006 %to 2018;

  %let pop_years = pop&year ;
  %let sum_years = pop&year;
  %let pop_age = (AGEGROUP ne '');
  %let n_years = 1;
  %let geogdenom = FIPS_STATE IN (16, 41, 53);
  %let pop_race_sex = (RACE = 1);

  /*CASE FILE VARIABLES**/
  %let dataset = Q ;
  %let year = (year=&year);
  %let age = (agegroup ne '') ;
  %let geognum = FIPS_STATE_RES IN (16, 41, 53);
  %let race = (race = 1) ;
  %let sex = (sex ne '') ;
  %let cause = QR = 1;

  /*OUTPUT DESCRIPTORS*/
  %let time = "&year";
  %let measure = ' Q Deaths';
  %let racesexage = 'Race1, Region Name, Both Sex, All Ages';

  %Race1RATE;
%end;
%mend; 
%loop;
 

 

[hlovejoy]

Thank you so much for the input- this is what i'm looking for. However, i get the following error when I run it. Any thoughts?

ERROR: The index variable in the %DO YEAR loop has taken on an invalid or missing value. The
macro will stop executing.
ERROR: The macro LOOP will stop executing.

[hlovejoy]

OK, I played with that a little more, and it is working now! I just had to rename the variable "year1". THANK YOU!

One small thing - In the output, it is giving the correct year in the description above the table, but not IN the table. In the table it gives the variable name. Thoughts on that?

Title:

' X Deaths', 'Race1, NW Region, Both Sex, All Ages', '2016'

Table:

X Deaths

Race1, NW Region, Both Sex, All Ages

&year1

56

291020

21.0327

15.7465

28.4176

5.28622

7.38494

291020

[hlovejoy]

OK I figured that out too 🙂 Thank you so much ChrisNZ!

I had to change it to double quotes " " instead of single quotes ' '. Which you totally had in your example buy I overlooked.

%let time = "&year1";

[ballardw]

This looks almost like attempting to make the macro language do some sort of proc summary with multiple class variables and a data step.

I might suggest providing a small example of your data and what you want for output from that data.

And what your Race1Rate macro looks like.

Note that setting many values outside of a macro and assuming that another macro, such as your Race1Rate macro, uses them correctly instead of passing them as parameters makes for a difficult debug job when something goes odd.