DATA Step, Macro, Functions and more

array of numbers longer than 8

Accepted Solution Solved
Reply
Contributor
Posts: 24
Accepted Solution

array of numbers longer than 8

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


Accepted Solutions
Solution
‎01-22-2012 08:30 PM
Super Contributor
Posts: 1,636

array of numbers longer than 8

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

View solution in original post


All Replies
PROC Star
Posts: 7,492

array of numbers longer than 8

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;

Contributor
Posts: 24

array of numbers longer than 8

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

Super User
Posts: 10,048

array of numbers longer than 8

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

PROC Star
Posts: 7,492

array of numbers longer than 8

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

Contributor
Posts: 24

array of numbers longer than 8

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

Super Contributor
Posts: 1,636

Re: array of numbers longer than 8

Do you want something like this?

data have;

input CUSTOMERID :QUOTENO Smiley TongueRODUCTID :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

Regular Contributor
Posts: 184

array of numbers longer than 8

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.

Trusted Advisor
Posts: 1,301

array of numbers longer than 8

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

Contributor
Posts: 24

array of numbers longer than 8

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

Solution
‎01-22-2012 08:30 PM
Super Contributor
Posts: 1,636

array of numbers longer than 8

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

Contributor
Posts: 24

array of numbers longer than 8

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

Contributor
Posts: 36

Re: array of numbers longer than 8

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 Smiley TongueRODUCTID :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

ObsCUSTOMERIDQUOTENOPRODUCTIDQUOTERANKNEW_RANK
1500011753.2411
2500011855.2721
3500011960.6733
4500021744.9611
5500021945.1721
6500022049.2733
7605011772.6511
8605011977.4522

50% reduction

ObsCUSTOMERIDQUOTENOPRODUCTIDQUOTERANKNEW_RANK
1500011753.2411
2500011855.2721
3500011960.6731
4500021744.9611
5500021945.1721
6500022049.2731
7605011772.6511
8605011977.4521

@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.

Contributor
Posts: 24

Re: array of numbers longer than 8

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

Contributor
Posts: 36

Re: array of numbers longer than 8

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.

🔒 This topic is solved and locked.

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

Discussion stats
  • 15 replies
  • 1516 views
  • 6 likes
  • 7 in conversation