## Need Two-Dimensional Array Help!

Solved
Frequent Contributor
Posts: 90

# Need Two-Dimensional Array Help!

Hello Everyone,

I am trying to use a two-dimensional array for a particular problem

This is the problem:

The data set orion.msp contains the average manufacturer’s suggested retail price for shoes, based on the product line and the product category. The product group ID is the last two digits of Prod_Cat_ID.

Listing of orion.msp

• orion.MSP

Prod_     Prod_ Avg_Suggested_

Obs     Line Cat_ID     Retail_Price

1       21 2101            .

2       21 2102          70.79

3       22 2201         173.79

4       22 2202         174.40

5       23 2301            .

6       23 2302            .

7       24 2401          29.63

8       24 2402         287.80

The data set orion.shoe_sales contains the Product_ID, the Product_Name, and the Total_Retail_Price for all of the shoes sold by Orion Star.

Partial Listing of orion.shoe_sales

Total_Retail_

Product_ID    Product_Name                                       Price

220200200024    Pro Fit Gel Gt 2030 Women's Running Shoes            \$178.50

220200100092    Big Guy Men's Air Terra Sebec Shoes                   \$83.00

240200100043    Bretagne Performance Tg Men's Golf Shoes L. \$282.40

220200300157    Hardcore Men's Street Shoes Large                    \$220.20

240200100051    Bretagne Stabilites 2000 Goretex Shoes \$420.90

220200100035    Big Guy Men's Air Deschutz Viii Shoes                \$125.20

220200100090    Big Guy Men's Air Terra Reach Shoes                  \$177.20

220200200018    Lulu Men's Street Shoes                              \$132.80

240200100052    Bretagne Stabilities Tg Men's Golf Shoes \$99.70

1. Create a data set named combine using a two-dimensional array to combine the table of values from orion.msp with orion.shoe_sales. Create a new variable named Manufacturer_Suggested_Price based on the values of product line and product category. The product line is the first two digits of the Product_ID variable. The product category ID is the third and fourth digits of the Product_ID variable.

This is my code so far:

data combine2 (keep=Product_ID Product_Name Total_Retail_Price Product_Line Product_Cat_ID MSP);

drop L C;

array avgprice{21:24,2} (0,70.79,

173.79,174.4,

0,0,

29.63,278.8);

set orion.msp;

set orion.shoe_sales (firstobs=1 obs=5);

Product_Line=substr(Product_ID,1,2); /*Using substr function to extract the number of the Group_ID*/

Product_Cat_ID=substr(Product_ID,4,1); /*Using substr function to extract the Catalog ID*/

L=Product_Line;

C=Prod_Cat_ID;

MSP=avgprice{L,C};

run;

What I keep getting in my log is:

ERROR: Array subscript out of range at line....

I am not sure where I am going wrong with my code at this point.

If anyone could look at this and let me know where I am going wrong with my code?

Thank you!

Alisa

Accepted Solutions
Solution
‎04-17-2012 01:29 AM
Super User
Posts: 10,784

## Re: Need Two-Dimensional Array Help!

```data shoe_sales;
input product_id \$ @17 product_name \$53. @70 total_retail_price dollar8.2;
cards;
220200200024    Pro Fit Gel Gt 2030 Women's Running Shoes            \$178.50
220200100092    Big Guy Men's Air Terra Sebec Shoes                   \$83.00
240200100043    Bretagne Performance Tg Men's Golf Shoes L.          \$282.40
220200300157    Hardcore Men's Street Shoes Large                    \$220.20
240200100051    Bretagne Stabilites 2000 Goretex Shoes               \$420.90
220200100035    Big Guy Men's Air Deschutz Viii Shoes                \$125.20
220200100090    Big Guy Men's Air Terra Reach Shoes                  \$177.20
220200200018    Lulu Men's Street Shoes                              \$132.80
240200100052    Bretagne Stabilities Tg Men's Golf Shoes             \$99.70
;
run;
data combine2 ;
drop L C;
array avgprice{21:24,2} _temporary_ (0,70.79,
173.79,174.4,
0,0,
29.63,278.8);
set shoe_sales ;
L=input(substr(Product_ID,1,2),best8.); /*Using substr function to extract the number of the Group_ID*/
C=input(substr(Product_ID,4,1),best1.); /*Using substr function to extract the Catalog ID*/
MSP=avgprice{L,C};
run;

```

Ksharp

All Replies
Posts: 1,318

## Re: Need Two-Dimensional Array Help!

data msp;

input prod_line prod_cat_id avg_suggested_retail_price;

cards;

21 2101            .

21 2102          70.79

22 2201         173.79

22 2202         174.40

23 2301            .

23 2302            .

24 2401          29.63

24 2402         287.80

;

run;

data shoe_sales;

input product_id \$ @17 product_name \$53. @70 total_retail_price dollar8.2;

cards;

220200200024    Pro Fit Gel Gt 2030 Women's Running Shoes            \$178.50

220200100092    Big Guy Men's Air Terra Sebec Shoes                   \$83.00

240200100043    Bretagne Performance Tg Men's Golf Shoes L.          \$282.40

220200300157    Hardcore Men's Street Shoes Large                    \$220.20

240200100051    Bretagne Stabilites 2000 Goretex Shoes               \$420.90

220200100035    Big Guy Men's Air Deschutz Viii Shoes                \$125.20

220200100090    Big Guy Men's Air Terra Reach Shoes                  \$177.20

220200200018    Lulu Men's Street Shoes                              \$132.80

240200100052    Bretagne Stabilities Tg Men's Golf Shoes             \$99.70

;

run;

COMBINE METHOD 1:

data combine;

if 0 then set msp;

declare hash msp(dataset:'msp');

msp.definekey('prod_line','prod_cat_id');

msp.definedata('avg_suggested_retail_price');

msp.definedone();

do until(done);

set shoe_sales end=done;

prod_line=substr(product_id,1,2);

prod_cat_id=substr(product_id,1,4);

msp.find();

output;

end;

stop;

run;

 prod_line prod_cat_id avg_suggested_retail_price product_id product_name total_retail_price 22 2202 174.4 22020020 Pro Fit Gel Gt 2030 Women's Running Shoes 178.5 22 2202 174.4 22020010 Big Guy Men's Air Terra Sebec Shoes 83 24 2402 287.8 24020010 Bretagne Performance Tg Men's Golf Shoes L. 282.4 22 2201 173.79 22010070 Armadillo Road Dmx Women's Running Shoes 99.7 22 2202 174.4 22020030 Hardcore Men's Street Shoes Large 220.2 24 2402 287.8 24020010 Bretagne Stabilites 2000 Goretex Shoes 420.9 22 2202 174.4 22020010 Big Guy Men's Air Deschutz Viii Shoes 125.2 22 2202 174.4 22020010 Big Guy Men's Air Terra Reach Shoes 177.2 22 2202 174.4 22020020 Lulu Men's Street Shoes 132.8 24 2402 287.8 24020010 Bretagne Stabilities Tg Men's Golf Shoes 99.7

COMBINE METHOD 2:

proc sql noprint;

select min(prod_line),

max(prod_line),

count(distinct prod_cat_id),

avg_suggested_retail_price

into :min_prod_line,

:max_prod_line,

rod_cat_dim,

:avgprices separated by ' '

from (  select prod_line,

substr(put(prod_cat_id,4.),4,1) as prod_cat_id,

avg_suggested_retail_price

from msp )

order by prod_line, prod_cat_id;

quit;

data combine2;

array avgprice[&min_prod_line:&max_prod_line,&prod_cat_dim] _temporary_ (&avgprices);

set shoe_sales;

prod_line=input(substr(product_id,1,2),2.);

prod_cat_id=input(substr(product_id,4,1),1.);

msp=avgprice[prod_line,prod_cat_id];

run;

 product_id product_name total_retail_price prod_line prod_cat_id msp 22020020 Pro Fit Gel Gt 2030 Women's Running Shoes 178.5 22 2 174.4 22020010 Big Guy Men's Air Terra Sebec Shoes 83 22 2 174.4 24020010 Bretagne Performance Tg Men's Golf Shoes L. 282.4 24 2 287.8 22010070 Armadillo Road Dmx Women's Running Shoes 99.7 22 1 173.79 22020030 Hardcore Men's Street Shoes Large 220.2 22 2 174.4 24020010 Bretagne Stabilites 2000 Goretex Shoes 420.9 24 2 287.8 22020010 Big Guy Men's Air Deschutz Viii Shoes 125.2 22 2 174.4 22020010 Big Guy Men's Air Terra Reach Shoes 177.2 22 2 174.4 22020020 Lulu Men's Street Shoes 132.8 22 2 174.4 24020010 Bretagne Stabilities Tg Men's Golf Shoes 99.7 24 2 287.8

COMBINE METHOD 3:

proc sql noprint;

create view _msp as

select prod_line,

substr(put(prod_cat_id,4.),4,1) as prod_cat_id,

avg_suggested_retail_price

from msp

order by prod_line, calculated prod_cat_id;

select min(prod_line),

max(prod_line),

count(distinct prod_cat_id),

avg_suggested_retail_price

into :min_prod_line,

:max_prod_line,

rod_cat_dim,

:avgprices separated by ' '

from _msp;

quit;

proc transpose data=_msp out=msp2(drop=_ prefix=avgprice;

var avg_suggested_retail_price;

run;

data combine3;

array avgprice[&min_prod_line:&max_prod_line,&prod_cat_dim];

if _n_=1 then set msp2;

set shoe_sales;

prod_line=input(substr(product_id,1,2),2.);

prod_cat_id=input(substr(product_id,4,1),1.);

msp=avgprice[prod_line,prod_cat_id];

drop avgprice:;

run;

 product_id product_name total_retail_price prod_line prod_cat_id msp 22020020 Pro Fit Gel Gt 2030 Women's Running Shoes 178.5 22 2 174.4 22020010 Big Guy Men's Air Terra Sebec Shoes 83 22 2 174.4 24020010 Bretagne Performance Tg Men's Golf Shoes L. 282.4 24 2 287.8 22010070 Armadillo Road Dmx Women's Running Shoes 99.7 22 1 173.79 22020030 Hardcore Men's Street Shoes Large 220.2 22 2 174.4 24020010 Bretagne Stabilites 2000 Goretex Shoes 420.9 24 2 287.8 22020010 Big Guy Men's Air Deschutz Viii Shoes 125.2 22 2 174.4 22020010 Big Guy Men's Air Terra Reach Shoes 177.2 22 2 174.4 22020020 Lulu Men's Street Shoes 132.8 22 2 174.4 24020010 Bretagne Stabilities Tg Men's Golf Shoes 99.7 24 2 287.8
Solution
‎04-17-2012 01:29 AM
Super User
Posts: 10,784

## Re: Need Two-Dimensional Array Help!

```data shoe_sales;
input product_id \$ @17 product_name \$53. @70 total_retail_price dollar8.2;
cards;
220200200024    Pro Fit Gel Gt 2030 Women's Running Shoes            \$178.50
220200100092    Big Guy Men's Air Terra Sebec Shoes                   \$83.00
240200100043    Bretagne Performance Tg Men's Golf Shoes L.          \$282.40
220200300157    Hardcore Men's Street Shoes Large                    \$220.20
240200100051    Bretagne Stabilites 2000 Goretex Shoes               \$420.90
220200100035    Big Guy Men's Air Deschutz Viii Shoes                \$125.20
220200100090    Big Guy Men's Air Terra Reach Shoes                  \$177.20
220200200018    Lulu Men's Street Shoes                              \$132.80
240200100052    Bretagne Stabilities Tg Men's Golf Shoes             \$99.70
;
run;
data combine2 ;
drop L C;
array avgprice{21:24,2} _temporary_ (0,70.79,
173.79,174.4,
0,0,
29.63,278.8);
set shoe_sales ;
L=input(substr(Product_ID,1,2),best8.); /*Using substr function to extract the number of the Group_ID*/
C=input(substr(Product_ID,4,1),best1.); /*Using substr function to extract the Catalog ID*/
MSP=avgprice{L,C};
run;

```

Ksharp

Frequent Contributor
Posts: 90

## Re: Need Two-Dimensional Array Help!

Thank you!! This worked perfectly!! :smileylaugh:

Posts: 1,318