Help using Base SAS procedures

Need Two-Dimensional Array Help!

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 90
Accepted Solution

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

  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

  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: 9,676

Re: Need Two-Dimensional Array Help!

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

View solution in original post


All Replies
Trusted Advisor
Posts: 1,300

Re: Need Two-Dimensional Array Help!

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_lineprod_cat_idavg_suggested_retail_priceproduct_idproduct_nametotal_retail_price
222202174.422020020Pro Fit Gel Gt 2030 Women's Running Shoes178.5
222202174.422020010Big Guy Men's Air Terra Sebec Shoes83
242402287.824020010Bretagne Performance Tg Men's Golf Shoes L.282.4
222201173.7922010070Armadillo Road Dmx Women's Running Shoes99.7
222202174.422020030Hardcore Men's Street Shoes Large220.2
242402287.824020010Bretagne Stabilites 2000 Goretex Shoes420.9
222202174.422020010Big Guy Men's Air Deschutz Viii Shoes125.2
222202174.422020010Big Guy Men's Air Terra Reach Shoes177.2
222202174.422020020Lulu Men's Street Shoes132.8
242402287.824020010Bretagne Stabilities Tg Men's Golf Shoes99.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,

  Smiley Tonguerod_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_idproduct_nametotal_retail_priceprod_lineprod_cat_idmsp
22020020Pro Fit Gel Gt 2030 Women's Running Shoes178.5222174.4
22020010Big Guy Men's Air Terra Sebec Shoes83222174.4
24020010Bretagne Performance Tg Men's Golf Shoes L.282.4242287.8
22010070Armadillo Road Dmx Women's Running Shoes99.7221173.79
22020030Hardcore Men's Street Shoes Large220.2222174.4
24020010Bretagne Stabilites 2000 Goretex Shoes420.9242287.8
22020010Big Guy Men's Air Deschutz Viii Shoes125.2222174.4
22020010Big Guy Men's Air Terra Reach Shoes177.2222174.4
22020020Lulu Men's Street Shoes132.8222174.4
24020010Bretagne Stabilities Tg Men's Golf Shoes99.7242287.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,

  Smiley Tonguerod_cat_dim,

  :avgprices separated by ' '

   from _msp;

quit;

proc transpose data=_msp out=msp2(drop=_Smiley Happy 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_idproduct_nametotal_retail_priceprod_lineprod_cat_idmsp
22020020Pro Fit Gel Gt 2030 Women's Running Shoes178.5222174.4
22020010Big Guy Men's Air Terra Sebec Shoes83222174.4
24020010Bretagne Performance Tg Men's Golf Shoes L.282.4242287.8
22010070Armadillo Road Dmx Women's Running Shoes99.7221173.79
22020030Hardcore Men's Street Shoes Large220.2222174.4
24020010Bretagne Stabilites 2000 Goretex Shoes420.9242287.8
22020010Big Guy Men's Air Deschutz Viii Shoes125.2222174.4
22020010Big Guy Men's Air Terra Reach Shoes177.2222174.4
22020020Lulu Men's Street Shoes132.8222174.4
24020010Bretagne Stabilities Tg Men's Golf Shoes99.7242287.8
Solution
‎04-17-2012 01:29 AM
Super User
Posts: 9,676

Re: Need Two-Dimensional Array Help!

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

Frequent Contributor
Posts: 90

Re: Need Two-Dimensional Array Help!

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

Trusted Advisor
Posts: 1,300

Re: Need Two-Dimensional Array Help!

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 673 views
  • 0 likes
  • 3 in conversation