How to define numeric array using character variables?

[ShashankB1987]

data sales;
input prod $ Jan_Units Feb_Units Mar_Units Jan_Price Feb_Price Mar_Price ;
cards;
A 408 323 168 10 10 12
B 632 631 100 15 10 14
C 619 677 285 14 15 11
D 846 136 831 15 13 13
E 518 440 630 12 11 13
;

Output Needs to be:

Product Jan_Total_sales Feb_Total_sales Mar_Total_sales
A 4080 3230 2016
B 9480 6310 1400
C 8666 10155 3135
D 12690 1768 10803
E 6216 4840 8190

run;

When I run the below code,

data sales2;
set sales;
array Units[3] Jan_Units-Jun_Units;
array Price[3] Jan_Price-Jun_Price;
array Total_Sales[3];
do i=1 to 3;
Total_Sales[i] = Units[i]*Price[i];
end;
run;

I get the below error;

ERROR: Missing numeric suffix on a numbered variable list (Jan_Units-Mar_Units).
ERROR: Too few variables defined for the dimension(s) specified for the array Units.

Can someone please advice me in the above mentioned error?

[FreelanceReinh]

Hello @ShashankB1987,

Use name range lists (with a double dash) in the array definitions:

array Units[*] Jan_Units--Mar_Units;
array Price[*] Jan_Price--Mar_Price;

(Leave it to SAS to determine the array dimension, based on the number of variables in the list, i.e., specify "[*]". Then you don't have to adjust it when you switch from March to June. Make sure, however, that Jan_Units, Feb_Units etc. appear in chronological order in the input dataset and also not mixed with Jan_Price, etc.)

[ballardw]

Because you have variable names that do not have a natural integer based sequence you cannot use the Var1-varn type list. That form expects sequentially  numbered variable names with the same base.

If the columns are adjacent in the data then you can use the two dash list:   Jan_Units -- Jun_Units; but this will work only if 1) the columns are sequential in the data set, i.e. Proc Contents reports the column numbers in sequence and 2) The variables already exist.

Second thing: In this line of code:

array Units[3] Jan_Units-Jun_Units;

Your data does not show any variable named Jun_units and if intended to use Jan, Feb, Mar, Apr, May AND Jun then why would you stick the 3 in the Units definition?

 Or just spell out all the names and skip lists.

You are placing data in variable names, Jan, Feb, Mar etc. This often indicates poor data structures and spreadsheet thinking which will add complexity to coding.

If you actually had provided a valid list and the number of elements does not match the array definition then you have another cause of errors. Example:

data junk;
  input x1 - x6;
datalines;
1 2 3 4 5 6
;

data want;
   set junk;
   array units[3] x1-x6;
run;

Will generate:

13   data want;
14      set junk;
15      array units[3] x1-x6;
ERROR: Too many variables defined for the dimension(s) specified for the array units.
16   run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.WANT may be incomplete.  When this step was stopped there were 0
         observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

[LeonidBatkhan · Accepted solution]

Hi ShashankB1987,

You can do it using the following solution:

data sales2 (keep=prod Jan_Total_sales Feb_Total_sales Mar_Total_sales);
   set sales;
   array units[3] Jan_Units Feb_Units Mar_Units;
   array price[3] Jan_Price Feb_Price Mar_Price;
   array total[3] Jan_Total_sales Feb_Total_sales Mar_Total_sales;
   do i=1 to dim(units);
      total[i] = units[i] * price{i];
   end;
run;

Hope this helps.

[Kurt_Bremser]

Your primary problem is bad data design.

From a dataset containing these variables

product month units price

it is a simple multiplication, and a wide report can easily be done by using month as ACROSS in PROC REPORT.

Maxim 19: Long Beats Wide.