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
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
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
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
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 |
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
Thank you!! This worked perfectly!! :smileylaugh:
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.