Using an array as a lookup table

[InfoAlisaA]

Hello Everyone,

So I have the following data that I am trying to transpose:

Obs Stat                              Product21 Product22 Product23 Product24

  1 Frequency                              66.000   277.000      . 18.000

  2 Mfg_Suggested_Retail_Price_Mean        70.788   174.292      . 173.056

  3 Mfg_Suggested_Retail_Price_Min         17.000    13.000      . 5.000

  4 Mfg_Suggested_Retail_Price_Max        130.000   385.000      . 398.000

  5 Mfg_Suggested_Retail_Price_Median      68.000   164.000      . 190.500

  6 Mfg_Suggested_Retail_Price_StdDev      21.731    71.703      . 141.389

This is the code that I have so far in order to do this:

data trans (keep=Statistic Price ProductLine); /*Creating SAS data set trans*/

   set orion.shoe_stats; /*Using orion.shoe_stats*/

   array Prod{*} _numeric_; /*Creating array Prod*/

   array StatS{1:6} $50. ('Frequency','Mfg_Suggested_Retail_Price_Mean','Mfg_Suggested_Retail_Price_Min','Mfg_Suggested_Retail_Price_Max'

                       ,'Mfg_Suggested_Retail_Price_Median','Mfg_Suggested_Retail_Price_StdDev');

   /*Creating StatS array with all of the different statistics

Using a DO loop as this was suggested through a website*/

do i=1 to dim(Prod);

Price=StatS{i};

Statistic=Prod{i};

output;

end;

run;

proc print data=shortrotate label;

label Price='Product';

run;

The problem that I am having is that I have to create a variable Product_Line that get the number of the product from the Product name.

I am not sure how to do this if I am supposed to put in a scan statement into my DO loop in order to scan for the number from the Product Name or what I am supposed to do.

I have tried this where I created a product_line array and then set it's value to i, but that didn't work:

data trans (keep=Statistic Price PL); /*Creating SAS data set trans*/

   set orion.shoe_stats; /*Using orion.shoe_stats*/

   array Prod{*} _numeric_; /*Creating array Prod*/

   array StatS{1:6} $50. ('Frequency','Mfg_Suggested_Retail_Price_Mean','Mfg_Suggested_Retail_Price_Min','Mfg_Suggested_Retail_Price_Max'

                       ,'Mfg_Suggested_Retail_Price_Median','Mfg_Suggested_Retail_Price_StdDev');

   product_line{*} _numeric_;

   /*Creating StatS array with all of the different statistics

Using a DO loop as this was suggested through a website*/

do i=1 to dim(Prod);

Price=StatS{i};

Statistic=Prod{i};

PL=product_line{i}

output;

end;

run;

proc print data=shortrotate label;

label Price='Product';

run;

If anyone has any suggestions, that would be greatly appreciated.

Here is what the final result supposed to be:

The TRANS data set

Product_

        Obs Stat Line                                Value

          1 Frequency 21                            66.000

          2 Frequency 22                             277.000

          3 Frequency 23                                 .

          4 Frequency 24                             18.000

          5 Mfg_Suggested_Retail_Price_Mean 21        70.788

          6 Mfg_Suggested_Retail_Price_Mean 22       174.292

          7 Mfg_Suggested_Retail_Price_Mean 23          .

          8 Mfg_Suggested_Retail_Price_Mean 24       173.056

          9 Mfg_Suggested_Retail_Price_Min 21        17.000

         10 Mfg_Suggested_Retail_Price_Min 22        13.000

         11 Mfg_Suggested_Retail_Price_Min 23          .

         12 Mfg_Suggested_Retail_Price_Min 24         5.000

         13 Mfg_Suggested_Retail_Price_Max 21       130.000

         14 Mfg_Suggested_Retail_Price_Max 22       385.000

         15 Mfg_Suggested_Retail_Price_Max 23          .

Thanks,

Alisa

[Linlin]

how about:

data have;

infile cards ;

input stat $ 33. Product21 Product22 procduct23 Product24;

cards;

Frequency                                           66.000   277.000    .  18.000

Mfg_Suggested_Retail_Price_Mean        70.788   174.292    .  173.056

Mfg_Suggested_Retail_Price_Min         17.000    13.000    .   5.000

Mfg_Suggested_Retail_Price_Max        130.000   385.000    .   398.000

Mfg_Suggested_Retail_Price_Median      68.000   164.000    .   190.500

Mfg_Suggested_Retail_Price_StdDev      21.731    71.703    .   141.389

;

data want (keep=stat line value);

  set have;

  array _p(*) pro:;

do _n_=1 to dim (_p);

line=compress(vname(_p(_n_)),'','kd');

value=_p(_n_);

output;

end;

proc print;run;

             Obs         stat                                            line     value

              1    Frequency                                          21      66.000

              2    Frequency                                          22     277.000

              3    Frequency                                           23        .

              4    Frequency                                            24      18.000

              5    Mfg_Suggested_Retail_Price_Mean       21      70.788

              6    Mfg_Suggested_Retail_Price_Mean       22     174.292

              7    Mfg_Suggested_Retail_Price_Mean       23        .

              8    Mfg_Suggested_Retail_Price_Mean       24     173.056

              9    Mfg_Suggested_Retail_Price_Min        21      17.000

             10    Mfg_Suggested_Retail_Price_Min        22      13.000

             11    Mfg_Suggested_Retail_Price_Min        23        .

             12    Mfg_Suggested_Retail_Price_Min        24       5.000

             13    Mfg_Suggested_Retail_Price_Max        21     130.000

             14    Mfg_Suggested_Retail_Price_Max        22     385.000

             15    Mfg_Suggested_Retail_Price_Max        23        .

             16    Mfg_Suggested_Retail_Price_Max        24     398.000

             17    Mfg_Suggested_Retail_Price_Median     21      68.000

             18    Mfg_Suggested_Retail_Price_Median     22     164.000

             19    Mfg_Suggested_Retail_Price_Median     23        .

             20    Mfg_Suggested_Retail_Price_Median     24     190.500

             21    Mfg_Suggested_Retail_Price_StdDev     21      21.731

             22    Mfg_Suggested_Retail_Price_StdDev     22      71.703

             23    Mfg_Suggested_Retail_Price_StdDev     23        .

             24    Mfg_Suggested_Retail_Price_StdDev     24     141.389

Linlin

[InfoAlisaA]

Hi Linlin,

Unfortunately, I cannot use the first part of your program because I cannot infile my data. My data set is in my library so I have to use my data set from my library.

I am also not sure what you mean by this line:

line=compress(vname(_p(_n_)),'','kd');

  What does 'kd' do?

I tried to put something like this into my code like this:

data trans (keep=Statistic Price Pline); /*Creating SAS data set trans*/

   set orion.shoe_stats; /*Using orion.shoe_stats*/

   array Prod{*} _numeric_; /*Creating array Prod*/

   array StatS{1:6} $50. ('Frequency','Mfg_Suggested_Retail_Price_Mean','Mfg_Suggested_Retail_Price_Min','Mfg_Suggested_Retail_Price_Max'

                       ,'Mfg_Suggested_Retail_Price_Median','Mfg_Suggested_Retail_Price_StdDev');

   array Pline{*} _numeric_;

   /*Creating StatS array with all of the different statistics

Using a DO loop as this was suggested through a website*/

do i=1 to dim(Prod);

Price=StatS{i};

Statistic=Prod{i};

Pline=compress(vname(Prod(i)),"",'kd');

output;

end;

run;

proc print data=trans label;

label Price='Product';

run;

Unfortunately, I got this in my log:

ERROR: Illegal reference to the array Pline.

Not sure where to go from here.

Thanks,

Alisa

[Linlin]

HI Alisa,

line=compress(vname(_p(_n_)),'','kd');

  What does 'kd' do?

compress(vname(_p(_n_)),'','kd') will keep the digits in a variable name. so for variables product21,procduct22,product23,product24 ,line will be 21,22,23,24.

what are the variables in your dataset orion.shoe_stats?

[InfoAlisaA]

Hi Linlin,

This is the way the data set looks like:

Obs Stat                              Product21 Product22 Product23 Product24

  1 Frequency                              66.000   277.000      . 18.000

  2 Mfg_Suggested_Retail_Price_Mean        70.788   174.292      . 173.056

  3 Mfg_Suggested_Retail_Price_Min         17.000    13.000      . 5.000

  4 Mfg_Suggested_Retail_Price_Max        130.000   385.000      . 398.000

  5 Mfg_Suggested_Retail_Price_Median      68.000   164.000      . 190.500

  6 Mfg_Suggested_Retail_Price_StdDev      21.731    71.703      . 141.389

Thanks,

Alisa

[Linlin]

try this one:

data trans (keep=Statistic Price Pline); /*Creating SAS data set trans*/

   length pline $ 2;

   set orion.shoe_stats; /*Using orion.shoe_stats*/

   array Prod{*} _numeric_; /*Creating array Prod*/

do i=1 to dim(Prod);

Price=prod{i};

Pline=compress(vname(Prod(i)),"",'kd');

output;

end;

run;

proc print data=trans label;

label Price='Product';

run;

[InfoAlisaA]

It works somewhat, but I still need to include the title of the statistics to get:

The TRANS data set

Product_

        Obs Stat Line       Value

          1 Frequency 21        66.000

          2 Frequency 22       277.000

          3 Frequency 23          .

          4 Frequency 24        18.000

          5 Mfg_Suggested_Retail_Price_Mean 21        70.788

          6 Mfg_Suggested_Retail_Price_Mean 22       174.292

          7 Mfg_Suggested_Retail_Price_Mean 23          .

          8 Mfg_Suggested_Retail_Price_Mean 24       173.056

          9 Mfg_Suggested_Retail_Price_Min 21        17.000

         10 Mfg_Suggested_Retail_Price_Min 22        13.000

         11 Mfg_Suggested_Retail_Price_Min 23          .

         12 Mfg_Suggested_Retail_Price_Min 24         5.000

         13 Mfg_Suggested_Retail_Price_Max 21       130.000

         14 Mfg_Suggested_Retail_Price_Max 22       385.000

         15 Mfg_Suggested_Retail_Price_Max 23          .

Thanks,

Alisa

[Linlin]

can you post your log file? maybe the variables in the keep option don't match the variables in your dataset.

data trans (keep=Statistic Price Pline);

[InfoAlisaA]

Hi Linlin,

This is my log file which looks okay:

1984  data trans (keep=Statistic Price Pline); /*Creating SAS data set trans*/

1985     set orion.shoe_stats;

1986     array Prod{*} _numeric_;

1987     array StatS{1:6} $50.

1987! ('Frequency','Mfg_Suggested_Retail_Price_Mean','Mfg_Suggested_Retail_Price_Min','Mfg_Sugge

1987! sted_Retail_Price_Max'

1988                         ,'Mfg_Suggested_Retail_Price_Median','Mfg_Suggested_Retail_Price_St

1988! dDev');

1989  do i=1 to dim(Prod);

1990  Price=StatS{i};

1991  Statistic=Prod{i};

1992  Pline=compress(vname(Prod{i}),"",'kd');

1993  output;

1994  end;

1995  run;

NOTE: There were 6 observations read from the data set ORION.SHOE_STATS.

NOTE: The data set WORK.TRANS has 24 observations and 3 variables.

NOTE: DATA statement used (Total process time):

      real time           0.02 seconds

      cpu time            0.01 seconds

1996

1997  proc print data=shortrotate label;

1998  label Price='Product';

1999  run;

NOTE: There were 24 observations read from the data set WORK.SHORTROTATE.

NOTE: PROCEDURE PRINT used (Total process time):

      real time           0.00 seconds

      cpu time

[MikeZdeb]

hi ... how about TRANSPOSE instead of the array approach ...

data have;

input stat : $33. product21-product24;

cards;

Frequency                              66.000   277.000    .  18.000

Mfg_Suggested_Retail_Price_Mean        70.788   174.292    .  173.056

Mfg_Suggested_Retail_Price_Min         17.000    13.000    .   5.000

Mfg_Suggested_Retail_Price_Max        130.000   385.000    .   398.000

Mfg_Suggested_Retail_Price_Median      68.000   164.000    .   190.500

Mfg_Suggested_Retail_Price_StdDev      21.731    71.703    .   141.389

;

proc transpose data=have out=want (rename=(col1=value));

by stat notsorted;

var product21-product24;

run;

data want (drop=_name_);

length line $2;

set want;

line = compress(_name_,,'kd');

run;

[InfoAlisaA]

Hi Mike,

I wish I could use proc transpose, but I can't.

Here is my code so far which isn't working:

data trans (keep=Statistic Price Pline); /*Creating SAS data set trans*/

   set orion.shoe_stats; /*Using orion.shoe_stats*/

   array Prod{*} _numeric_; /*Creating array Prod*/

   array StatS{1:6} $50. ('Frequency','Mfg_Suggested_Retail_Price_Mean','Mfg_Suggested_Retail_Price_Min','Mfg_Suggested_Retail_Price_Max'

                       ,'Mfg_Suggested_Retail_Price_Median','Mfg_Suggested_Retail_Price_StdDev');

   array Pline{*} _numeric_;

   /*Creating StatS array with all of the different statistics

Using a DO loop as this was suggested through a website*/

do i=1 to dim(Prod);

Price=StatS{i};

Statistic=Prod{i};

Pline=compress(vname(Prod(i)),"",'kd');

output;

end;

run;

proc print data=trans label;

label Price='Product';

run;

I am getting an illegal reference on my Pline array.

If you have any suggestions on what to do, that would be great.

Thanks,

Alisa

[MikeZdeb]

hi ... I'm curious ... why can't you use TRANSPOSE (it seems as if it produces the data set you wanted)

[InfoAlisaA]

Hi Mike,

It's for a homework assignment for an Avanced SAS training course, so I have to follow the instructions in the assignment.

Thanks,

Alisa

[art297]

Take a look at the last approach suggested at: http://www.sconsig.com/sastips/tip00392.htm

It may or may not be relevant, but is definitely a way to use an array as a lookup table.