BookmarkSubscribeRSS Feed
npa
Calcite | Level 5 npa
Calcite | Level 5

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;

6 REPLIES 6
art297
Opal | Level 21

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.

data_null__
Jade | Level 19

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;

Fisher
Quartz | Level 8

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;

npa
Calcite | Level 5 npa
Calcite | Level 5

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!

data_null__
Jade | Level 19
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.

Reeza
Super User

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;

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
  • 6 replies
  • 1769 views
  • 0 likes
  • 5 in conversation