BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
chrisengel
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
chrisengel
Obsidian | Level 7

@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.

 

View solution in original post

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

chrisengel
Obsidian | Level 7
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?
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

chrisengel
Obsidian | Level 7

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?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

"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.

chrisengel
Obsidian | Level 7
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?
chrisengel
Obsidian | Level 7
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
chrisengel
Obsidian | Level 7
I have a different question that I'll just make a new thread for since none of these solutions will work actually
chrisengel
Obsidian | Level 7

@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.

 

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
  • 9 replies
  • 1927 views
  • 0 likes
  • 2 in conversation