topic Re: Using an array as a lookup table in SAS Procedures

Using an array as a lookup table

InfoAlisaA — Mon, 09 Apr 2012 22:03:20 GMT

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

Re: Using an array as a lookup table

Linlin — Mon, 09 Apr 2012 22:49:11 GMT

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

Re: Using an array as a lookup table

MikeZdeb — Mon, 09 Apr 2012 23:11:22 GMT

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;

Re: Using an array as a lookup table

art297 — Mon, 09 Apr 2012 23:36:44 GMT

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.

Re: Using an array as a lookup table

InfoAlisaA — Tue, 10 Apr 2012 21:49:51 GMT

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

Re: Using an array as a lookup table

InfoAlisaA — Tue, 10 Apr 2012 21:51:32 GMT

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

Re: Using an array as a lookup table

Linlin — Tue, 10 Apr 2012 22:37:55 GMT

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?

Re: Using an array as a lookup table

MikeZdeb — Tue, 10 Apr 2012 23:13:48 GMT

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

Re: Using an array as a lookup table

InfoAlisaA — Wed, 11 Apr 2012 01:53:56 GMT

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

Re: Using an array as a lookup table

InfoAlisaA — Wed, 11 Apr 2012 01:54:56 GMT

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

Re: Using an array as a lookup table

Linlin — Wed, 11 Apr 2012 02:21:41 GMT

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;

Re: Using an array as a lookup table

InfoAlisaA — Thu, 12 Apr 2012 17:54:03 GMT

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

Re: Using an array as a lookup table

Linlin — Thu, 12 Apr 2012 18:12:59 GMT

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

Re: Using an array as a lookup table

InfoAlisaA — Thu, 12 Apr 2012 23:13:09 GMT

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