topic Re: Dynamically declare multi-dimensional array -- Alphabetic prefixes are different, too few variab in SAS Programming

Dynamically declare multi-dimensional array -- Alphabetic prefixes are different, too few variables

chrisengel — Fri, 09 Dec 2016 10:25:49 GMT

Hello SAS community,

I have multiple datasets with variables in the following format:

group1I1-group1IY

group2I1-group2IY

....

groupXI1-groupXIY

My program processes with these variables by declaring a two-dimensional array:

array group {&x, &y} group1I1-group&x.I&y;

This declaration fails. It works fine when I do it manually -- i.e., group1I1-group1I&y group2I1-group2I&y etc etc , but when I try to do it like above, i get the error:

ERROR: Alphabetic prefixes for enumerated variables (group1I1-group23I118) are different.

ERROR: Too few variables defined for the dimension(s) specified for the array group

The macro is going to be run for many different sets, where the &x and &y will be changing, and thus I need to be able to declare the array more dynamically.

Is there some syntax in the array declaration I am getting wrong, or do I have to write some macro within the macro to concatenate the group&x.I series separately?

Thanks for any assistance.

Re: Dynamically declare multi-dimensional array -- Alphabetic prefixes are different, too few variab

RW9 — Fri, 09 Dec 2016 10:33:07 GMT

The reason is that:
group1I != group23I

Simple as that. The text prefix has to be the same,the number are the end is the only part which can change. Now if your variables are all there and in order, you could use:

array group {&x, &y} group1I1--group&x.I&y;

Two dashes mean variables from that one to that as positional. This is dangerous as they might not be in order.

TBH, you would be better off re-modelling your data. Put groups going down the dataset:

From:
group1l1 group1l2...

to:

Group:

1L1

1L2

...

You will find your programming easier. For instance you could stack all your datasets together, then just process one dataset.

Re: Dynamically declare multi-dimensional array -- Alphabetic prefixes are different, too few variab

chrisengel — Fri, 09 Dec 2016 10:40:08 GMT

how does that fix the problem of being able to declare the arrays for the groups across x and y though?

If i just rename all the groupxIy variables to xIy and try to declare the array dynamically again, isn't it then back to the same problem?

Re: Dynamically declare multi-dimensional array -- Alphabetic prefixes are different, too few variab

RW9 — Fri, 09 Dec 2016 10:44:52 GMT

How does "what" fix the problem? In the first example, I use -- which means positional parameter1 to positional paramter X, with each variable between in the order they apper in the dataset.

In the second example I am talking about normalising the data from wide which you have, to long, i.e. all the groups god down the datastet, so another example:

ABC1L1 ... ABC13L1 ... ABC15L5...

11 56 8

To:

GROUP RESULT

ABC1L1 11

...

ABC13L1 56

...

ABC15L15 8

...

You can then stack all your datasets into one, maybe have another variable called variable. Then you can do your processing on this one dataset.

Re: Dynamically declare multi-dimensional array -- Alphabetic prefixes are different, too few variab

chrisengel — Fri, 09 Dec 2016 10:53:04 GMT

Oh, that won't work. The data needs to be wide and I need to be able to arrange the variables so I can declare them dynamically in array. Additionally, the double-dash doesnt work since they aren't in proper order and it causes an over-declaration of too many vars.

I guess I can do some kind of loop like this instead:

%let arrayvar=;
%do i=1 %to &x;
%do j=1 %to &y;
%let arrayvar=&arrayvar group1I.&y-group&i.I&y;
%end;
%end;
array group{&x, &y} &arrayvar;

I haven't tested this I'm just proposing it as an idea, since I know the declaration of the variables this way works, just dont know if dynamically it can be achieved like above?

Re: Dynamically declare multi-dimensional array -- Alphabetic prefixes are different, too few variab

RW9 — Fri, 09 Dec 2016 11:02:47 GMT

"The data needs to be wide" - why? The way you program has nothing to do with any inputs or outputs from the program. You are just making your life a lot harder by retaining that method of working.

And your example code given will not work either. The variable names themselves are the problem. To use syntax such as:

variable<suffix>-variable<suffix>

The variable in the above needs to be the same, and as we have established:

group1I != group23I

The only other way I can think of doing it would be to create a full list of variables from the metdata:

proc sql noprint;
  select distinct NAME
  into    :VLIST separated by " "
  from   (select  *
            from    DICTIONARY.COLUMNS
            where LIBNAME="WORK"
              and  MEMNAME="<yourdataset>"
              and substr(NAME,1,5)="GROUP"
            order by NAME);
quit;

data want;
  set <yourdataset>;
  array group{&x.,&y.} &vlist.;
run;

As you can see, the coding for this is geting more and more complicated, all due to poor data modelling choices.

Re: Dynamically declare multi-dimensional array -- Alphabetic prefixes are different, too few variab

chrisengel — Fri, 09 Dec 2016 12:24:19 GMT

the sql gives me an error at "order by name" thinking and warns "assuming symbol OR was misspelled as order"

if all the group variables are listed in order in the declared array as a double-dimensional, will it even reference properly with the dimensions?

Re: Dynamically declare multi-dimensional array -- Alphabetic prefixes are different, too few variab

chrisengel — Fri, 09 Dec 2016 12:33:47 GMT

I think I have an idea.

The double-dash method should work as long as I get the variables ordered properly, right?

So if I reorder everything alphabetically in the dataset and just doubledash across group* then it ought to be "safe" to declare the array

Re: Dynamically declare multi-dimensional array -- Alphabetic prefixes are different, too few variab

chrisengel — Fri, 09 Dec 2016 13:05:48 GMT

I have a different question that I'll just make a new thread for since none of these solutions will work actually

Re: Dynamically declare multi-dimensional array -- Alphabetic prefixes are different, too few variab

chrisengel — Fri, 09 Dec 2016 13:34:46 GMT

@RW9 wrote:

And your example code given will not work either. The variable names themselves are the problem.

So I actually have the solution now, and a variation of the code I provided works just fine:

%macro test;
%let varlist=;
%let j=118;
%do i=1 %to 23;
%let arrayvar&i=group&i.I1-group&i.I&j;
%end;
%do i=1 %to 23;
%let varlist=&varlist &&arrayvar&i;
%end;
%put &varlist;
%mend;

%test;

The 23 and 118 i grab dynamically through datasets so I replace those with &x and &y in the code and works like a charm.

The varlist variable contains a nice list in proper order of the relevant dimensions of variables to declare the array ina clean way:

data want;
set have;
array group{&x, &y} &varlist;
...
run;

I provide this code in case others need to dynamically declare arrays using specific dimensions of existing variables in a dataset that don't conform to a nice alphabetical format.