topic Re: Need Two-Dimensional Array Help! in SAS Procedures

Need Two-Dimensional Array Help!

InfoAlisaA — Mon, 16 Apr 2012 23:08:36 GMT

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

220100700024 Armadillo Road Dmx Women's Running Shoes $99.70

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

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

Re: Need Two-Dimensional Array Help!

FriedEgg — Tue, 17 Apr 2012 00:11:07 GMT

REPRESENT YOUR EXAMPLE DATA:

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

220100700024 Armadillo Road Dmx Women's Running Shoes $99.70

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,

:prod_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,

:prod_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

Re: Need Two-Dimensional Array Help!

Ksharp — Tue, 17 Apr 2012 05:29:30 GMT

How about:

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
220100700024    Armadillo Road Dmx Women's Running Shoes             $99.70
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

Re: Need Two-Dimensional Array Help!

InfoAlisaA — Tue, 17 Apr 2012 17:41:23 GMT

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

Re: Need Two-Dimensional Array Help!

FriedEgg — Tue, 17 Apr 2012 17:54:38 GMT

Ksharp's method matches the first way I show, except I dynamically build the array information based on the file MSP instead of explicitly assigning it. Based on the way you wrote your original code I believe you were most likely attempting to use the third method I show, using a double SET statement, where you take the MSP data and SET it into the ARRAY statement and then perform look-up for each row in SHOE_SALES.