array of numbers longer than 8

[Jamie]

Hi Folks

I'm attempting to create an array of numbers which are 9 long.

I'm getting the following error:

ERROR 352-185: The length of numeric variables is 3-8.

Is there a way to format the numbers within an array?

Thanks in advance

Jamie

[art297]

You have to tell us more.  As you can see, in the following example, arrays can easily handle numbers that contain 9 or more digits.

data have;

  input x y z;

  cards;

123456789 1234567890 12345678901

;

data want;

  set have;

  array numbers x--z;

  do over numbers;

    total+numbers;

  end;

run;

[Jamie]

Hi Art

Thank you for your reply. Ill explain what im trying to do.

I have a table with a list of unique ProductIds. I want to pass each ProductID to a macro and run the macro.

I was attempting to create an array of the productIDs and pass the array to the macro.

is this possible or would I have to pass the ProductIds over 1 at a time?

Thanks again

[Ksharp]

Your ProductID variable is numberic or character?

data have;
input id @@;
  cards;
123456789 1234567890 12345678901
;
run;
data _null_;
 set have;
 call symputx(cats('id',_n_),id);
run;
%put _user_;

 

Ksharp

[art297]

There a probably much simpler ways to accomplish what you are trying to do but, if you are interested in combining arrays and macros, take a look at: http://www2.sas.com/proceedings/sugi22/CODERS/PAPER80.PDF

[Jamie]

Thanks for your replies.

Art, there propbably are better ways of doing what im doing but it's fairly complex and I know some people frown upon people posting without having tried themselves.

Ill try explain what im doing - feel free to ignore though as it could take more time than you have to give:

We have a comparison website where a customer submits a request for a quotation and we return multiple quotes per customer to choose from. Each customer can have multiple quotations and multiple prices returned per quote.

Example of table:

CUSTOMERID      QUOTENO      PRODUCTID      QUOTE      RANK

5000                    1                    17                      53.24          1

5000                    1                    18                      55.27          2

  5000                    1                    19                      60.67          3

5000                    2                    17                      44.96          1

5000                    2                    19                      45.17          2

  5000                    2                    20                      49.27          3

  6050                    1                    17                      72.65          1

  6050                    1                    19                      77.45          2

So from above, Customer 5000 submitted 2 quotation requests. ProductID 17 was the cheapest (rank 1), product 18 was second cheapest and so on.

Customer 6050 also submitted a quote and 2 quotations were given.

We are trying to do some "what if" analysis. So if ProductID 19 reduced their price by 5% how would it affect the RANK of their price.

A new variable in the table containing the new RANK based on 5% reduction would be enough for us to build an OLAP cube off the raw table.

If you can suggest any concepts on how to do this then it would be greatly appreciated. If not dont worry.

Thanks again

Jamie

[Linlin]

Do you want something like this?

data have;

input CUSTOMERID :QUOTENO :PRODUCTID :QUOTE :rank;

cards;

5000                    1                    17                      53.24          1

5000                    1                    18                      55.27          2

5000                    1                    19                      60.67          3

5000                    2                    17                      44.96          1

5000                    2                    19                      45.17          2

5000                    2                    20                      49.27          3

6050                    1                    17                      72.65          1

6050                    1                    19                      77.45          2

;

run;

proc sort data=have out=temp;

  by  CUSTOMERID QUOTENO QUOTE ;

run;

data temp;

  set temp;

  by CUSTOMERID QUOTENO;

  if last.QUOTENO then quote=quote*.50; /* to show the difference, take a 50% reduction*/

proc sort data=temp;

by CUSTOMERID QUOTENO QUOTE ;

data want;

   set temp;

   by CUSTOMERID QUOTENO;

   if first.QUOTENO then new_rank=1;

     output;

       new_rank+1;

  run;

options nocenter;

proc print;run;

Obs    CUSTOMERID    QUOTENO    PRODUCTID     QUOTE    rank    new_rank

1        5000          1           19       30.335      3         1

2        5000          1           17       53.240      1         2

3        5000          1           18       55.270      2         3

4        5000          2           20       24.635      3         1

5        5000          2           17       44.960      1         2

6        5000          2           19       45.170      2         3

7        6050          1           19       38.725      2         1

8        6050          1           17       72.650      1         2

Linlin

[Howles]

The stored LENGTH of a numeric variable is pretty much independent of the number of decimal digits in its formatted representation.

You apparently used a LENGTH statement to override the default stored length of 8. Don't do that.

Note: Advice is different for a character variable.

[FriedEgg]

To expand upon Howles comments:

SAS Stores numbers in floating point representation.  This means that the basic unit of storage for a numeric variable is the bit.  Bits are groups in sequences of 8, or a byte.  SAS stores numeric data using a maximum of 64 bits (8 bytes) to hold three pieces of information: the sign (1 bit), exponent (11 bits) and mantissa (12-52/53/56 bits, depends on OS and functional equivalency).  Each integer value of length in SAS refers to a byte of storage and effects the amount of bits you allow for the mantissa.  The largest possible integer you can store in a SAS numeric column without sacrificing precision is 9,007,199,254,750,992 (under windows or unix, this number is larger on IBM mainframe because of differences not worth explaining here).

Hopefully this makes it more clear.

Thanks,

FriedEgg

[Jamie]

Hi Linlin,

Your suggestion is close - what your code does is show the new rank based on the most expensive productID reducing their price.

What im looking to do is report on each ProductID individually. So if ProductID 17 reduced their price by .50 then their new rank would (still) be 1. If productID 18 reduced their price by .50 then their new rank would be 1. If ProductID 19 reduced their price by .50 the new rank would be 1. Its difficult because each reduced price needs to be compared to the original prices as its a view for each company separately.

If you can spare the time to make any further suggestion it would be greatly appreciated. Sorry if im not explaining this very well.

Thanks again

Jamie

[Linlin · Accepted solution]

Hi Jamie,

rank1 is the the new rank after changing the price of rank 1 product, and rank3 is the new rank after changing the rank 3 product.

hope it helps.

/* add  variable new_quote as a 50% price reduction for all products */

data have1;

   set have;

     new_quote=quote*.5;

          run;

proc transpose data=have1 out=look (drop=_name_ ) prefix=original_rank;

by CUSTOMERID QUOTENO;

var new_QUOTE;

run;

data want;

   merge have1 look;

   by CUSTOMERID QUOTENO;

   run;

data want (keep=CUSTOMERID QUOTENO PRODUCTID QUOTE:rank);

  set want;

  quote1=quote;

  quote2=quote;

  quote3=quote;

  if rank=1 then quote1=original_rank1;

    if rank=2 then quote2=original_rank2;

               if original_rank2=. then quote2=.;

            if rank=3 then quote3=original_rank3;

                if original_rank3=. then quote3=.;

          run;

data quote1;

  set want(drop=quote2 quote3);

proc sort;

   by CUSTOMERID QUOTENO  QUOTE1;

data quote1;

   set quote1;

     by CUSTOMERID QUOTENO;

           if first.QUOTENO then rank1=1;

              output;

                    rank1+1;

proc sort;

by CUSTOMERID QUOTENO  rank;

run;

/***********************************/

data quote2;

  set want(drop=quote1 quote3);

proc sort;

   by CUSTOMERID QUOTENO  QUOTE2;

data quote2;

   set quote2;

     by CUSTOMERID QUOTENO;

           if first.QUOTENO then rank2=1;

              output;

                    rank2+1;

  proc sort;

    by CUSTOMERID QUOTENO  rank;

run;

/***********************************/

data quote3;

  set want(drop=quote2 quote1);

proc sort;

   by CUSTOMERID QUOTENO  QUOTE3;

data quote3;

   set quote3;

     by CUSTOMERID QUOTENO;

           if first.QUOTENO then rank3=1;

              output;

                    if quote3=. then rank3=.; else rank3+1;

proc sort;

by CUSTOMERID QUOTENO  rank;

run;

data final;

   merge quote1 quote2 quote3;

  by CUSTOMERID QUOTENO  rank;

run;

proc print;run;

Obs CUSTOMERID QUOTENO PRODUCTID QUOTE rank quote1 rank1 quote2 rank2 quote3 rank3

1     5000       1        17    53.24   1  26.620   1   53.240   2   53.240   2

2     5000       1        18    55.27   2  55.270   2   27.635   1   55.270   3

3     5000       1        19    60.67   3  60.670   3   60.670   3   30.335   1

4     5000       2        17    44.96   1  22.480   1   44.960   2   44.960   2

5     5000       2        19    45.17   2  45.170   2   22.585   1   45.170   3

6     5000       2        20    49.27   3  49.270   3   49.270   3   24.635   1

7     6050       1        17    72.65   1  36.325   1   72.650   2     .      1

8     6050       1        19    77.45   2  77.450   2   38.725   1     .      .

Linlin

[Jamie]

Thanks very much Linlin, this is great.

One more question if you have the time to spare?

Is there a way of having only 1 new column shown in the output rather than them being parsed individually?

I.E. output which would look like this:

CUSTOMERID     QUOTENO     PRODUCTID     QUOTE     RANK     NEW_RANK

5000                    1                    17                    53.24          1          1

5000                    1                    18                    55.27          2          1

5000                    1                    19                    60.67          3          1

5000                    2                    17                    44.96          1          1

5000                    2                    19                    45.17          2          1

5000                    2                    20                    49.27          3          1

6050                    1                    17                    72.65          1          1

6050                    1                    19                    77.45          2          1

This looks strange I know, however when the percentage is reduced from the example 50% to say 5% and the rest of the PRODUCTIDs are added (of which there are around 60) then it provides the exact requirement for our row-level OLAP cube.

Thanks again for your help (everyone), much appreciated

[PAzevedo]

If i understood you well, you want to evaluate the new rank of each reduced product quote in comparisson to the other products original quote.

Starting from Linlin initial coding, i'd sugest a different approach with some macro coding and hash search:

options mprint;

* Value of the reduction (%);
%let reduction=5;

data have;
input CUSTOMERID :QUOTENO :PRODUCTID :QUOTE :RANK;
cards;
5000                    1                    17                      53.24          1
5000                    1                    18                      55.27          2
5000                    1                    19                      60.67          3
5000                    2                    17                      44.96          1
5000                    2                    19                      45.17          2
5000                    2                    20                      49.27          3
6050                    1                    17                      72.65          1
6050                    1                    19                      77.45          2
;
run;

proc transpose data=have out=have_transposed (drop=_name_ ) prefix=quote;
by CUSTOMERID QUOTENO;
var quote;
run;

* Max group of products quoted;
proc sql noprint;
select max(numproducts)
into :maxproducts
from (
select count(*) as numproducts
from have
group by customerid, quoteno);
quit;

%put ### &maxproducts ###;

%macro newRank;

data new_ranks;
length quote1-quote%trim(&maxproducts) 8;
set have;
if _n_=1 then do;
  dcl hash hh (dataset:'have_transposed');
  hh.definekey('customerid','quoteno');
  hh.definedata(all:'yes');
  hh.definedone();
end;
new_rank=1;
new_quote=quote*(1-&reduction/100);
hh.find();
%do i=1 %to &maxproducts;
  if quote&i & new_quote>quote&i then
   new_rank+1;  
%end;
drop quote1-quote%trim(&maxproducts) new_quote;
run;

proc print data=new_ranks;
run;

%mend newRank;

%newRank;

The output:

5% reduction

Obs	CUSTOMERID	QUOTENO	PRODUCTID	QUOTE	RANK	NEW_RANK
1	5000	1	17	53.24	1	1
2	5000	1	18	55.27	2	1
3	5000	1	19	60.67	3	3
4	5000	2	17	44.96	1	1
5	5000	2	19	45.17	2	1
6	5000	2	20	49.27	3	3
7	6050	1	17	72.65	1	1
8	6050	1	19	77.45	2	2

50% reduction

Obs	CUSTOMERID	QUOTENO	PRODUCTID	QUOTE	RANK	NEW_RANK
1	5000	1	17	53.24	1	1
2	5000	1	18	55.27	2	1
3	5000	1	19	60.67	3	1
4	5000	2	17	44.96	1	1
5	5000	2	19	45.17	2	1
6	5000	2	20	49.27	3	1
7	6050	1	17	72.65	1	1
8	6050	1	19	77.45	2	1

@edit:
You can easly change the reduction value in the initial parameter.

Also, i didn't see any limit on products in one quote so i assumed a client can ask for more than 3 in one.

[Jamie]

Hi PAzevedo

Thank you very much for your reply. You understood me perfectly and your code does exactly what I want!

I have certainly added hash object lookup to my list of "things to use in the future". It runs very quickly indeed.

For my personal benefit could you explain this part please?

if quote&i & new_quote>quote&i then

   new_rank+1;

How does the second ampersand work in this statement?

Thank you very much for your suggestion, and once again to everyone else who took time to help me.

Jamie

[PAzevedo]

Jamie wrote:
Hi PAzevedo
Thank you very much for your reply. You understood me perfectly and your code does exactly what I want!
I have certainly added hash object lookup to my list of "things to use in the future". It runs very quickly indeed.
For my personal benefit could you explain this part please?
if quote&i & new_quote>quote&i then
   new_rank+1; 
How does the second ampersand work in this statement?
Thank you very much for your suggestion, and once again to everyone else who took time to help me.
Jamie

You mean the operator "&"? It's equivelent to "and" operator as "|" is equivelen to "or" operator.

Since the number of quoted products is different in each quote you have to test if the quote you're analizing was made and, if so, you increase the new rank each time an original quote is less than or equal to the new quote.

You may have to test the behaviour of the program for products with the same quote inside the same quoteno in order to refine the comparison operator "<".

As a note about hash objects, it haves a limitation of the memory available for each SAS session (not system memory). Since all the hashed dataset is loaded into memory the program will break if not enough memory is available.

Regards,

PA.