topic Re: Specify a differing range of columns in an array in SAS Programming

Specify a differing range of columns in an array

bignate1030 — Thu, 27 Jun 2019 17:22:22 GMT

I have a table that looks like

Col1 Col2 Col3 Col4 ...

-----------------------------------------

4 2 1 5

I need to calculate some mins and maxes of the columns in my table, however the number of columns will change every time and I need to start at the third column, the name of which also changes. I know there exists the code for static arrays but is there some way I can modify that for my needs like:

ARRAY X{ncol(my_table)-2} Col3-Last;

Thank you

Re: Specify a differing range of columns in an array

PeterClemmensen — Thu, 27 Jun 2019 17:27:01 GMT

Here is one way

data have;
   array x {10};
   do i=1 to 100;
      do j=1 to dim(x);
         x[j]=rand('integer', 1, 10);
      end;
      output;
   end;
run;

proc sql noprint;
   select name into :vars separated by ' ' 
   from dictionary.columns
   where libname='WORK' & memname='HAVE' & varnum ge 3;
quit;

%put &vars.;

data want;
   set have;
   array MyArray{*} &vars.;
   /* More Code */
run;

Re: Specify a differing range of columns in an array

ballardw — Thu, 27 Jun 2019 17:31:31 GMT

@bignate1030 wrote:

I have a table that looks like

Col1 Col2 Col3 Col4 ...

-----------------------------------------

4 2 1 5

I need to calculate some mins and maxes of the columns in my table, however the number of columns will change every time and I need to start at the third column, the name of which also changes. I know there exists the code for static arrays but is there some way I can modify that for my needs like:
ARRAY X{ncol(my_table)-2} Col3-Last;
Thank you

Not anything dynamic but if the variables already exist in your data and the columns are adjacent as you imply you can use the -- (that is 2 dashes) list operator to define the array:

array x col3varname -- lastvarname.

You do not need to specify the size of the array if you use existing variables .

data example;
   set sashelp.class;
   array m age -- weight;
   x= max(of m(*));
   y= min(of m(*));
run;

Re: Specify a differing range of columns in an array

bignate1030 — Thu, 27 Jun 2019 17:46:31 GMT

The variables already exist but I don't know how many there are. I am analyzing variables at different time frames, so I have a table that looks like this:

Person Event Var_X_100_Days Var_X_6_Months.

2 3 1 2

And I can calculate min, max for that variable. However once I've done that I need to do the same for Var_Y. But the table for Var_Y looks like

Person Event Var_Y_6_Months Var_Y_1_Year Var_Y_2_Years Var_Y_3_Years

3 3 12 4 1 6

So I can't define a static range such as Var_X_100_Days -- Var_X_6_Months because the first and last variables will almost always have different names depending on what variable I am analyzing. However I know that the first variable will always be the third column and the last variable will always be the last column.

Re: Specify a differing range of columns in an array

Reeza — Thu, 27 Jun 2019 17:49:51 GMT

You have a data structure problem. It will be much easier to help if you can show what you're starting with and what you want to end up with. With some actual data. There are often short cut ways to refer to variables that can help.

If you always know the variable are from 3 to end though it can be very easy if that's the only logic you need to incorporate. Just pull that information from the sashelp.vtable using varnum, getting number 3 and the largest value and then creating macro variables.

then declare your array as

array myArray(*) thirdVar -- lastVar;
And you're fine.

If all start with the same prefix, as in your example above you can also simplify that by not using the sashelp table at all.

array myArray(*) var_: ;

The colon tells SAS to take all variables that start with Var_.

Re: Specify a differing range of columns in an array

PeterClemmensen — Thu, 27 Jun 2019 17:50:17 GMT

@bignate1030 have you tried my code?

Also, if the variables of interest have a common prefix, you can simply do this

data want;
   set have;
   array MyArray{*} Var:;
   /* More Code */
run;

Re: Specify a differing range of columns in an array

ballardw — Thu, 27 Jun 2019 17:54:20 GMT

@bignate1030 wrote:

The variables already exist but I don't know how many there are. I am analyzing variables at different time frames, so I have a table that looks like this:

Person Event Var_X_100_Days Var_X_6_Months.

2 3 1 2

And I can calculate min, max for that variable. However once I've done that I need to do the same for Var_Y. But the table for Var_Y looks like

Person Event Var_Y_6_Months Var_Y_1_Year Var_Y_2_Years Var_Y_3_Years

3 3 12 4 1 6

So I can't define a static range such as Var_X_100_Days -- Var_X_6_Months because the first and last variables will almost always have different names depending on what variable I am analyzing. However I know that the first variable will always be the third column and the last variable will always be the last column.

If Person and Event are character variables then you can use:

Array m _numeric_;

Otherwise you're going to have to use something like @PeterClemmensen shows.

Or possibly reconsider how your data is structured or generating names like Var_Y_6_Months