topic Re: Summing all numeric variables (flexible names and amount of variables) in SAS Programming

Summing all numeric variables (flexible names and amount of variables)

Quadraaa — Mon, 10 Oct 2011 19:14:27 GMT

Hello, everybody!

I have a program which generates a data set with one character variable and several numeric variables from data values with proc transpose. After that I need to add a variable to my data set which is equal to the sum of all these generated variables. The problem is that the names and amount of these variables are always different and I cant't get how to make SAS sum them I was googling all the evening long but haven't found the solution

Your help would be highly appreciated!

P.S. This is my first post here, so, please, be patient if I I posted my question in the wrong place or if the solution seems obvious for you, I am just learning SAS

Summing all numeric variables (flexible names and amount of variables)

Tom — Mon, 10 Oct 2011 19:18:44 GMT

data want;

set have;

total = sum(of _numeric_);

run;

Re: Summing all numeric variables (flexible names and amount of variables)

data_null__ — Mon, 10 Oct 2011 20:28:18 GMT

Tom wrote:
data want;
set have;
  total = sum(of _numeric_);
run;

It should be noted that with this syntax TOTAL will also appear on the RIGHT and will be summed. Of course in this example the value is always missing when the sums take place so all is well.

However, there could be problems with other functions particularly NMISS. It might be prudent to make a more carefully defined list.

I like to use arrays to create lists where I have more control of the elements, consider this example. Using the unexecuted SET is unnecessary but illustrates a more general solution where the list is derived in a more complex way.

data class;
   if 0 then set sashelp.class(keep=_numeric_);
   array v
 
_numeric_;
   set sashelp.class;
   nmiss1 = nmiss(of _numeric_);
   nmiss2 = nmiss(of v
);
   sum    = sum(of v
);
   n      =   n(of v
);
   run;
proc print;
   run;

Obs Age Height Weight Name Sex nmiss1 nmiss2 sum n

1 14 69.0 112.5 Alfred M 1 0 195.5 3

2 13 56.5 84.0 Alice F 1 0 153.5 3

3 13 65.3 98.0 Barbara F 1 0 176.3 3

4 14 62.8 102.5 Carol F 1 0 179.3 3

5 14 63.5 102.5 Henry M 1 0 180.0 3

6 12 57.3 83.0 James M 1 0 152.3 3

7 12 59.8 84.5 Jane F 1 0 156.3 3

8 15 62.5 112.5 Janet F 1 0 190.0 3

9 13 62.5 84.0 Jeffrey M 1 0 159.5 3

10 12 59.0 99.5 John M 1 0 170.5 3

11 11 51.3 50.5 Joyce F 1 0 112.8 3

12 14 64.3 90.0 Judy F 1 0 168.3 3

13 12 56.3 77.0 Louise F 1 0 145.3 3

14 15 66.5 112.0 Mary F 1 0 193.5 3

15 16 72.0 150.0 Philip M 1 0 238.0 3

16 12 64.8 128.0 Robert M 1 0 204.8 3

17 15 67.0 133.0 Ronald M 1 0 215.0 3

18 11 57.5 85.0 Thomas M 1 0 153.5 3

19 15 66.5 112.0 William M 1 0 193.5 3

Summing all numeric variables (flexible names and amount of variables)

Peter_C — Tue, 11 Oct 2011 07:03:32 GMT

beware use of _numeric_ in a general context as a data set might bring in only _character_ variables.

One (fairly) safe solution

Array v(*) _n_ _numeric_ ;

Should you wish to ignore that _n_ in do loops, just

Do index = 2 to dim(v);

As _n_ will always be numeric

num_total = sum( of v(*))-_ n_;

purists will point to the rare possibility that _n_ can become too large for integer precision and risk the num_total becoming incorrect.

Peter

Summing all numeric variables (flexible names and amount of variables)

data_null__ — Tue, 11 Oct 2011 11:40:24 GMT

Good point that _NUMERIC_ might be NULL and SAS will not like that. However adding a variable to unsure the array has at least one element would not be compatible with the statistic functions in general. Guess we can't have out cake and eat it too.:smileygrin: Unless we resort to code gen.

Summing all numeric variables (flexible names and amount of variables)

Peter_C — Tue, 11 Oct 2011 12:58:48 GMT

not hard to test an array having only one element to avoid trying to produce those stats.

In that kind of way maybe we should avoid producing a deviation of one, etc

Summing all numeric variables (flexible names and amount of variables)

data_null__ — Tue, 11 Oct 2011 14:04:27 GMT

Peter.C wrote:

not hard to test an array having only one element to avoid trying to produce those stats.

In that kind of way maybe we should avoid producing a deviation of one, etc

That's not the problem I was thinking about. Your example uses _N_ which is OK I suppose but it should be set to missing or better still use a DUMMY numeric that is always missing. Then you don't have to "correct" any of the results except NMISS or CMISS. At least I don't think any of the other statistic functions would need correcting.

Re: Summing all numeric variables (flexible names and amount of variables)

SASJedi — Tue, 11 Oct 2011 14:06:43 GMT

This is a great place to use a little SQL and macro to more precisely controlwhich variables you sum up later. For my example, I'll use the CLASS data set in the SASHELP library.

1. First execute this SQL code (which can be used as a template) to get a list of all the numeric variables in the target dataset, collected a macro variable named VARLIST:

proc sql noprint;
select name into :Varnames separated by ' '
  from dictionary.columns
  where LIBNAME='SASHELP' 
    and MEMNAME='CLASS'
    and TYPE='num';
quit;

2. Next, use the macro variable to write the list for you in the subsequent DATA step code:

data want;
   set sashelp.class;
   total=sum(of &varnames);
run;

Partial results below:

Obs	Name	Sex	Age	Height	Weight	total
1	Alfred	M	14	69.0	112.5	195.5
2	Alice	F	13	56.5	84.0	153.5
3	Barbara	F	13	65.3	98.0	176.3
4	Carol	F	14	62.8	102.5	179.3
5	Henry	M	14	63.5	102.5	180.0

Summing all numeric variables (flexible names and amount of variables)

Peter_C — Tue, 11 Oct 2011 15:13:00 GMT

have you any protection against the source data set having no numeric variables?

Re: Summing all numeric variables (flexible names and amount of variables)

Tom — Tue, 11 Oct 2011 15:27:27 GMT

total = sum(of total &varnames);

Remember add a %LET varnames=; statement BEFORE the SQL query so that any existing list of values is not retained in the macro variable. PROC SQL will not modify the value of the target macro variable when no rows are selected.

Of course this gets us back to the original reply.

total=sum(of _numeric_);

Summing all numeric variables (flexible names and amount of variables)

FriedEgg — Tue, 11 Oct 2011 16:28:50 GMT

%macro vnumlist(lib, sds, svar);

%global g_vnumsum;

proc sql noprint;

select count(*) into :vnobs

from sashelp.vcolumn

where libname="%upcase(&lib)"

and memname="%upcase(&sds)"

and type='num';

%if &vnobs>0 %then

%do;

select name into :vnames separated by ' '

from sashelp.vcolumn

where libname="%upcase(&lib)"

and memname="%upcase(&sds)"

and type='num';

%let g_vnumsum=&svar=sum(of &vnames);

%end;

%else

%do;

%let g_vnumsum=%str();

%put WARNING: No Numeric Variables to Sum;

%end;

quit;

%mend;

%vnumlist(sashelp, class, total)

data want;

set sashelp.class;

&g_vnumsum;

run;

Re: Summing all numeric variables (flexible names and amount of variables)

Tom — Tue, 11 Oct 2011 16:48:29 GMT

If you want to create a macro you can make it more efficient.

First you do not need to query twice to find the number of variables. SAS will set the automatic variable SQLOBS with the count.

Second can depend it not changing the value of the existing variable when there are no rows selected.

%macro vnumlist(lib, sds, svar);
%global g_vnumsum;
%let g_vnumsum=;
proc sql noprint;
   select name into :g_vnumsum separated by ' '
     from dictionary.columns
     where libname="%upcase(&lib)"
       and memname="%upcase(&sds)"
       and type='num'
   ;
quit;
%if &sqlobs %then %let g_vnumsum=&svar = sum(of &g_vnumsum); 
%else %put WARNING: No Numeric Variables to Sum;
%mend vnumlist;

Summing all numeric variables (flexible names and amount of variables)

FriedEgg — Tue, 11 Oct 2011 17:10:26 GMT

Nicely done Tom, thanks.

Re: Summing all numeric variables (flexible names and amount of variables)

SASJedi — Tue, 11 Oct 2011 20:51:20 GMT

A more sophisticated macro application with extensive parameter checking would provide protection against this and more... I thought I'd keep it simple as the original poster stated he was just learning SAS, and I didn't want to over complicate the issue. However, something like this would be a good start towards a more robust app:

 
%macro SumNums(DSN_In,DSN_Out, Sumvar);
%local Varnames LIB DSN;
  /* Did the user provide an input dataset name? */
%if &DSN_In= %then %do;
   %put ERROR: (SumNums) You must provide the name of the data set you wish to process.;
   %SyntaxHelp:
   %put NOTE- SYNTAX:  %NRSTR(%%SumNums%(DSN_In,DSN_Out,Sumvar%));
   %put NOTE-          DSN_In =Name of data set to process;
   %put NOTE-          DSN_Out=Name of output data set;
   %put NOTE-          Sumvar =Variable name to hold the sum (default is Total);
   %put NOTE- Example: %NRSTR(%%SumNums%(sashelp.class,work.test%));
   %GoTo EndMacro;
%end;
%let DSN_In=%QUPCASE(&DSN_In);

  /* Did the user ask for syntax help? */
%if &DSN_In=!HELP %then %do;
   %GoTo SyntaxHelp;
%end;
  /* Did the user provide an output dataset name? */
%if &DSN_Out= %then %do;
   %put ERROR: (SumNums) You must provide the name of the output data set.;
   %GoTo SyntaxHelp;
%end;
%let DSN_Out=%QUPCASE(&DSN_Out);
  /* Is the sum variable given a valid SAS variable name? */
%if &sumvar= %then %let Sumvar=Total;
%if not %sysfunc(nvalid(%superq(Sumvar))) %then %do;
   %put ERROR: (SumNums) %superq(Sumvar) is not a valid SAS variable name.;
   %GoTo SyntaxHelp;
%end;

%let DSN=%QSCAN(&DSN_In,2,.);
%if &DSN= %then %do;
   %let DSN=%SUPERQ(DSN_In);
   %let LIB=WORK;
%end;
%else %let LIB=%QSCAN(&DSN_In,1,.);

proc sql noprint;
select name into :Varnames separated by ' '
  from dictionary.columns
  where LIBNAME="&LIB" 
    and MEMNAME="&DSN"
    and TYPE='num'
;
quit;

  /* Were there any numeric variables in the input data set? */
%if &SQLOBS=0 %then %do;
   %put WARNING: (SumNums) dataset &DSN_In contained no numeric variables.;
   %GoTo EndMacro;
%end;
  /* Do the deed! */
data &DSN_out;
   set &DSN_In;
   &Sumvar=sum(of &varnames);
run;

%EndMacro:
%Mend SumNums;

After compiling, you just call the macro to do the deed.

Example of a call for SYNTAX help:

%SumNums(!HELP)

Example of a call to calculate the total for SASHELP.CLASS:

%SumNums(sashelp.class,work.test,MySumVar)

Re: Summing all numeric variables (flexible names and amount of variables)

Tom — Tue, 11 Oct 2011 22:27:25 GMT

I like the improved usability of using a single parameter to specify the dataset name versus separate libname and membername fields.

You are testing the value of the parameters before you have protected them with quoting functions.
You should check all of the parameters you can before bailing out.
You might want to test for existence of the input dataset.
You might want to exclude &SUMVAR from the list of variables to include in the sum.
For even more usability you can establish defaults for some parameters. To me the only required parameter here is the target variable name. The default to the input dataset could be &syslast. The default for the output dataset could be null string so that SAS will create DATAnn where nn is the next number after the last DATAnn that it created.

Re: Summing all numeric variables (flexible names and amount of variables)

Quadraaa — Wed, 12 Oct 2011 16:51:36 GMT

Wow, gentlemen, I am really surprised with how detailed and useful your answers are When I see these tricks I have two contradictory feelings: 1) I feel upset about my poor knowledge of SAS and 2) I feel glad that there are so many interesting things to learn

Thanks a lot to all of you.

Summing all numeric variables (flexible names and amount of variables)

FriedEgg — Wed, 12 Oct 2011 18:28:25 GMT

I have to send a kudos to SASJedi as well for going well beyond in his response what I would consider necessary and giving you not just an answer to point you in a direction but a real process to utilize.

Re: Summing all numeric variables (flexible names and amount of variables)

slsnx2 — Wed, 20 May 2015 16:21:16 GMT

I have to share my code:

I know it's a closed topic but I think this could be useful.

/************************************************************************************/

/* Descripcion general del programa: */

/* Suma las variables numericas de una tabla, sin especificar nombres. */

/* Autor : Saul Sanchez Sánchez */

/************************************************************************************/

data a;

do i=1 to 100;

a=abs(int(rand('NORMAL')*1000));

b=abs(int(rand('NORMAL')*1000));

c=abs(int(rand('NORMAL')*1000));

cl='a'||substr(left(compress(i)),1,1);

output;

end;

run;

data vars(where=(type='N'));

length name $ 8 type $ 1;

drop dsid i num rc;

dsid=open("work.a","i");

num=attrn(dsid,"nvars");

do i=1 to num;

name=varname(dsid,i);

type=vartype(dsid,i);

output;

end;

rc=close(dsid);

run;

data vars(keep=function);

set vars;

function='sum('||compress(name)||') as '||compress(name);

run;

proc sql;

select function into :function separated by ','

from vars;

quit;

%put &function;

proc sql;

create table b as

select cl, &function

from a

group by 1;

run;