topic Re: How to capture the value of a variable in a macro variable? in SAS Programming

How to capture the value of a variable in a macro variable?

npa — Mon, 12 Sep 2011 15:40:36 GMT

I am computing a total using proc means and want to use this total, which is called xtot and is the sum of x, in my subsequent data step.

I would like to do this without having to merge work.tot with test.second. Is this possible? As seen in the code below, I had the idea of achieving this using a global macro variable. The problem, of course, is that xtotl will resolve to 'xtot' not the value 5. I cannot apply the %eval function here either. I may have to resort to merging but want to know if it is possible to implement my idea of using global macro variables. I have some knowledge regarding SAS macros but am by no means an expert. All suggestions welcome.

proc means data=test.first noprint;

var x; /* where x=5, for example */

output out=work.tot (drop=_type_ _freq_)

sum (x)=xtot;

%global xtotl;

%let xtotl=xtot;

run;

data nextstep;

set test.second;

totl=xtotl;

run;

Re: How to capture the value of a variable in a macro variable?

art297 — Mon, 12 Sep 2011 15:52:12 GMT

Since you apparently only have one value for xtotl, you have a couple of options.

The simplest, I think, is to skip proc means altogether and get your xtotl from proc sql, directly, as a macro variable. You could do that with (or something very close to):

proc sql noprint;

select sum(x) into :xtotl

from test.first

;

quit;

Then, the way you would refer to it in your datastep would be:

data nextstep;

set test.second;

totl=&xtotl.;

run;

Of course, there are also numerous other ways of accomplishing the same thing, like merging the output file from proc means together with test.second, or using a data _null_ step and call symput to create the macro variable.

Re: How to capture the value of a variable in a macro variable?

data_null__ — Mon, 12 Sep 2011 17:09:35 GMT

Don't use a macro variable for this, it is bad technique. Use SET. With SET there is no data loss and it is easy to include other statistics.

proc means data=sashelp.class noprint;
   output out=work.tot (drop=_type_ _freq_) sum(age)=xtot;
   run;
data class;
   set sashelp.class;
   if _n_ eq 1 then set tot;
   run;
proc print;
   run;

How to capture the value of a variable in a macro variable?

Fisher — Mon, 12 Sep 2011 22:50:04 GMT

Try this:

proc means data=test.first noprint;

var x; /* where x=5, for example */

output out=work.tot (drop=_type_ _freq_)

sum (x)=xtot;

run;

data _null_;

set tot;

call symputx('xtotl', xtot, 'G' );

run;

data nextstep;

set test.second;

totl=xtotl;

run;

Re: How to capture the value of a variable in a macro variable?

npa — Tue, 13 Sep 2011 15:54:46 GMT

Fisher, for whatever reason, I could not get the data _null_/call symputx solution to work. Xtotl was missing for all observations. Not being well-versed with the call symput/symputx function, I was not sure how to debug the code.

data _null_, I originally used the code that you suggested, and it worked. What didn't work is that I need to grab that single value and use it in a proc report as part of a calculation. Specifically, I need to calculate a new variable that uses this single value, along with the row sums that get calculated at proc report run time, within proc report. I am new to proc report and my code is evolving as I try to determine the best (i.e., most efficient in terms of processing time and efficient code) way to do create this report. Hence, in my question above I wanted to use the macro variable in a data step but realized it can and should be used in the proc report. Perhaps all of this could be done within proc report? I don't know yet. I am curious to know what makes using a macro variable bad technique in this case.

art297, I was able to use the proc sql code successfully! Proc report (as well as a data step) recognized the macro variable and it resolved properly as shown in the log.

Thanks for all of the above suggestions!

Re: How to capture the value of a variable in a macro variable?

data_null__ — Tue, 13 Sep 2011 16:10:09 GMT

data _null_, I originally used the code that you suggested, and it worked. What didn't work is that I need to grab that single value and use it in a proc report as part of a calculation. Specifically, I need to calculate a new variable that uses this single value, along with the row sums that get calculated at proc report run time, within proc report. I am new to proc report and my code is evolving as I try to determine the best (i.e., most efficient in terms of processing time and efficient code) way to do create this report. Hence, in my question above I wanted to use the macro variable in a data step but realized it can and should be used in the proc report. Perhaps all of this could be done within proc report? I don't know yet. I am curious to know what makes using a macro variable bad technique in this case.

Using a macro variable as you describe implies you are not thinking about the problem properly. I have a general run of thumb "don't put data into macro variables". There are situations where data can go into macro variables but this isn't one them. There is also a potential data loss issue.

As for PROC REPORT if you could show an example that would be helpful. I'm not good a guessing what you're thinking. I doubt you will need a macro variable here either.

Re: How to capture the value of a variable in a macro variable?

Reeza — Tue, 13 Sep 2011 16:58:15 GMT

Do it all in one step, a SQL step. Not sure how big the data is, but this does the merge automatically:

proc sql;

create table class as

select *, sum(age) as age_total

from sashelp.class;

quit;

*ONLY TO Check age total;

proc means data=sashelp.class n mean sum;

var age;

run;