topic Re: dynamically assign the greatest number in a variable list in SAS Programming

dynamically assign the greatest number in a variable list

GoBlueSAS — Thu, 02 Jul 2020 21:35:30 GMT

I am using SAS 9.4 I would like to sum a number of variables, pop1 to popNN, where the greatest number, NN, will differ with every run. Is there a way to reference a number dynamically in a sum(of pop1--popNN) function? Can I use a macro variable here, like sum(of pop1--pop&macrovariable.) ?

thanks.

Re: dynamically assign the greatest number in a variable list

mkeintz — Thu, 02 Jul 2020 21:38:32 GMT

You can do something like this:

data want;
   set have;
   sum_of_pop=sum(of pop:);
run;

which tells SAS to sum all variables whose name begins with the letters "POP". SAS will figure out how many such variables there are.

Re: dynamically assign the greatest number in a variable list

Reeza — Thu, 02 Jul 2020 21:39:32 GMT

Here is a reference that illustrates how to refer to variables and datasets in a short cut list:
https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html

You can use colon wildcard option here, assuming you have a unique and consistent prefix.

sum(of pop:)

@GoBlueSAS wrote:

I am using SAS 9.4 I would like to sum a number of variables, pop1 to popNN, where the greatest number, NN, will differ with every run. Is there a way to reference a number dynamically in a sum(of pop1--popNN) function? Can I use a macro variable here, like sum(of pop1--pop&macrovariable.) ?

thanks.

Re: dynamically assign the greatest number in a variable list

ChrisNZ — Fri, 03 Jul 2020 01:00:25 GMT

The colon wildcard is the best option if the prefixes are suitable.

Otherwise, if you can create a missing variable after POPnn (let's call it LASTVAR), and if the POP variables are consecutive, you can use

sum (of POP1--LASTVAR)

Re: dynamically assign the greatest number in a variable list

Kurt_Bremser — Fri, 03 Jul 2020 07:01:27 GMT

You should also consider working with a long dataset layout, where you have only one variable and sum vertically as needed.

Re: dynamically assign the greatest number in a variable list

s_lassen — Fri, 03 Jul 2020 08:57:06 GMT

Yet another take on this.

The colon wildcard ,sum(of pop:), is the simplest, but it may not work if you have other variables that begin with POP.

The double dash approach ,sum(of pop--lastvar) only works if the POP variables are last in your input data.

A more safe approach may be this:

data want;
  retain pop1-pop99 .;
  set have;
  sum=sum(of pop1-pop99);
run;

That way, all the variables will exist, and the order of the variables on the dataset or the names of other variables do not matter.

EDIT: changed the retain value from 0 to missing, so that means or standard deviations will also be calculated correctly.

Re: dynamically assign the greatest number in a variable list

mkeintz — Sat, 04 Jul 2020 03:37:34 GMT

@s_lassen wrote:

Yet another take on this.

The colon wildcard ,sum(of pop:), is the simplest, but it may not work if you have other variables that begin with POP.

The double dash approach ,sum(of pop--lastvar) only works if the POP variables are last in your input data.

A more safe approach may be this:
data want;
  retain pop1-pop99 .;
  set have;
  sum=sum(of pop1-pop99);
run;
That way, all the variables will exist, and the order of the variables on the dataset or the names of other variables do not matter.

This is a good warning and a good solution, but if the user only started out with say POP1-POP59, they would now have 40 excess variables in the output dataset.

One might get around this by:

data want;
  set have;
  sum=sum(of pop1:, of pop2:, of pop3:, of pop4:, of pop5:, of pop6:, of pop7:, of pop8:, of pop9:);
run;

It's a bit wordy but:

It wouldn't include other variables with names like POP_US, POP_WORLD, etc.
It would sum up all the variables of interest, and
it wouldn't inadvertently create any extra variables in the output dataset.