Accessing corresponding numeric value from a dataset using macro variable inside a macro

[Tom]

So now run the updated program. Add the line:

options mprint;

Before the macro call.  The MPRINT lines in the LOG will show you what code your macro generates. Try to figure out how to have it generate the code you actually want.

[ballardw]

Let's try this one more time:
Proc print will not let you do calculations in the code. Example:

534  proc print data=sashelp.class;
535
536  heightweight = height/weight;
     ------------
     180
ERROR 180-322: Statement is not valid or it is used out of proper order.
537  run;

So this proc print step is going to Fail:

	proc print data=new_Results;
	id n %scan(&vars., &i.);
	inputval=INPUT(SYMGET(%scan(&vars., &i.));
	IR=inputval/n;
	IR_1K=IR*1000;
	LC=quantile('chisq',0.025, inputval*2)/(n*2);
	UC=quantile('chisq',0.975, (inputval+1)*2)/(n*2);
	res_l=LC*1000;
	res_u=UC*1000;
	title "rate per 1K  another way";
	run;

Fail. Period. End of story. Doesn't matter what data set is used, what erroneous macro variables may be involved, every statement with an = shown is an error.

[sigma_exp]

thank you for clarifying --you are absolutely right. In fact, it is because you pointed it out, I was able to find the bug. I am posting the updated code below. It fixes the problem. Thanks again!

[sigma_exp]

So here is the updated macro I wrote.  It fixes the problem and generates the output I was looking for. Thank you all for providing valuable feedback and input to get me to write this one.

%macro rate1(dat,vars);
%do i = 1 %to %sysfunc(countw(&vars.));
	%put %scan(&vars., &i.);
	*%LET newv=%scan(&vars., &i.);

	data new_Results; 
	set &dat.;
	proc genmod data=&dat.;
	model  %scan(&vars., &i.) = / offset=ln dist=poisson lrci;
	estimate 'Mean' intercept 1;
	title " cases for in &newv";
	run;



	data new_Results2;
	set &dat.;
	IR=%scan(&vars., &i.)/n;
	IR_1K=IR*1000;
	LC=quantile('chisq',0.025, %scan(&vars., &i.)*2)/(n*2);
	UC=quantile('chisq',0.975, (%scan(&vars., &i.)+1)*2)/(n*2);
	res_l=LC*1000;
	res_u=UC*1000;
	run;
	
	
	proc print data=new_Results2;
	id n %scan(&vars., &i.);
	title "rate per 1K  another way";
	run;

%END;

%mend rate1;

[Tom]

Why do you have the first two of these three lines? 

	data new_Results; 
	set &dat.;
	proc genmod data=&dat.;

What do you think the first two lines are doing?

[sigma_exp]

So the way I understood it:

	data new_Results; 
	set &dat.;
	proc genmod data=&dat.;

the new_Results is created based on the dataset parameter (dat) as defined in the original macro;

and then this, in turn, is being used as input dataset for PROC GENMOD   to generate the parameter estimates which is stored in the data new_Results 

[Tom]

No. Those two lines a making a new dataset that is a copy of &DAT. Then you are using the original &DAT as the input to the PROC. Then later, in the code you fixed, you are overwriting the dataset that your first two line data step created.

Just remove those two lines and avoid wasting time making a dataset that is never used.

[sigma_exp]

Okay, great advice, thank you, Tom...I will remove those two lines to make it more efficient

[Tom · Accepted solution]

So what is the purpose of the macro?

It seems to be running GENMOD. 

And then also doing some calculations on and printing the results of those calculations.

It looks like you have included the ability to pass in multiple variable names and repeat the process for each.  In that case you might want to limit what variables you print in that last PROC PRINT (or what variables are created in the data step before it).  Otherwise each of those PROC PRINTS will include ALL of the variables in the original dataset, and not just the ones related to the current variable being analyzed.

You also might want to learn how to use %LOCAL macro variables.  It can make the coding a little easier to type, understand and maintain.

%macro rate1(dat,vars);
%local i var ;
%do i = 1 %to %sysfunc(countw(&vars.));
  %LET var=%scan(&vars., &i.);


title "GENMOD for in &var";
proc genmod data=&dat.;
  model  &var = / offset=ln dist=poisson lrci;
  estimate 'Mean' intercept 1;
run;

data new_Results2;
  set &dat.;
  IR=&var/n;
  IR_1K=IR*1000;
  LC=quantile('chisq',0.025, &var*2)/(n*2);
  UC=quantile('chisq',0.975, (&var+1)*2)/(n*2);
  res_l=LC*1000;
  res_u=UC*1000;
run;

title "rate per 1K  for &VAR another way";
proc print data=new_Results2;
  id n &var;
  var IR IR_1K LC UC res_l rec_u;
run;

title;
%END;

%mend rate1;

[sigma_exp]

amazing...this is far more elaborate and easy to understand -- thanks a ton for providing a great example

[Reeza]

If you really want to improve this, remove the macro entirely by transposing your data and using BY groups. It'll be faster and you get one output data set to deal with that's nice and clean.

You would need to add a transpose to your current process and then a BY statement to your GENMOD and that's it.

BTW the way you're doing this would only leave you with the results from the last run, not all runs.

[sigma_exp]

Thank you Reeza for proposing the alternative solution, but would you please care to elaborate why is that: "BTW the way you're doing this would only leave you with the results from the last run, not all runs."? Because when I am looking at the output in the SAS window, I see the results corresponding to the specific variable during each iteration. And the estimates are also different for each loop.

[Reeza]

You'll see it in the output window, if that's all you're using your fine. But if your process errors out at this step for any reason, then it won't get replaced in the loop and will use the old values. This is a bad practice because it's easy to get the wrong results in the output window and not realize it. 

If the steps before work, it is getting overwritten so this data set would only have the results from the last run, not each run so if you want these values to do anything with in another step you do not have them anymore. 

data new_Results2;
  set &dat.;
  IR=&var/n;
  IR_1K=IR*1000;
  LC=quantile('chisq',0.025, &var*2)/(n*2);
  UC=quantile('chisq',0.975, (&var+1)*2)/(n*2);
  res_l=LC*1000;
  res_u=UC*1000;
run;

---

@sigma_exp wrote:
Thank you Reeza for proposing the alternative solution, but would you please care to elaborate why is that: "BTW the way you're doing this would only leave you with the results from the last run, not all runs."? Because when I am looking at the output in the SAS window, I see the results corresponding to the specific variable during each iteration. And the estimates are also different for each loop.

---