Help using Base SAS procedures

Using an array as a lookup table

Reply
Frequent Contributor
Posts: 90

Using an array as a lookup table

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

Super Contributor
Posts: 1,636

Re: Using an array as a lookup table

Posted in reply to InfoAlisaA

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

Frequent Contributor
Posts: 90

Re: Using an array as a lookup table

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

Super Contributor
Posts: 1,636

Re: Using an array as a lookup table

Posted in reply to InfoAlisaA

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?

Frequent Contributor
Posts: 90

Re: Using an array as a lookup table

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

Super Contributor
Posts: 1,636

Re: Using an array as a lookup table

Posted in reply to InfoAlisaA

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;

Frequent Contributor
Posts: 90

Re: Using an array as a lookup table

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

Super Contributor
Posts: 1,636

Re: Using an array as a lookup table

Posted in reply to InfoAlisaA

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);

Frequent Contributor
Posts: 90

Re: Using an array as a lookup table

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

Valued Guide
Posts: 765

Re: Using an array as a lookup table

Posted in reply to InfoAlisaA

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;

Frequent Contributor
Posts: 90

Re: Using an array as a lookup table

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

Valued Guide
Posts: 765

Re: Using an array as a lookup table

Posted in reply to InfoAlisaA

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

Frequent Contributor
Posts: 90

Re: Using an array as a lookup table

Hi Mike,

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

Thanks,

Alisa

PROC Star
Posts: 7,489

Re: Using an array as a lookup table

Posted in reply to InfoAlisaA

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.

Ask a Question
Discussion stats
  • 13 replies
  • 527 views
  • 7 likes
  • 4 in conversation