Function prod in SAS

[DoumbiaS]

Dear all, 

As SAS don't have a prod() function acting like sum() function, could anyone give an idea about how to achieve this ? I have the variables VAR1, TIME, VALUE and I want to get the variable VAR_WANT.

I know that I could use exp(sum(log())), but I can't because I have null and non positive raw values.

Thanks in advance.

VAR1    TIME       VALUE        VAR_WANT
F1        0        v1 = .5      v1*v2*v3*v4 = 0.5*0*0.7*1  
F1        1        v2 = 0       v2*v3*v4 = 0*0.7*1
F1        2        v3 = .7      v3*v4 = 0.7*1
F1        3        v4 = 1       v4 = 1
F2        0        w1 = 1.3     w1*w2*w3 = 1.3*(-0.94)*0.4
F2        1        w2 = -.94    w2*w3 = (-0.94)*0.4
F2        2        w3 = .4      w3 = .4

[ballardw]

So, describe what to do when you do have a "null" (not a SAS concept, do you mean missing?) or non-positive raw values?

Do you have access to Proc IML? This really smells like it want a matrix solution not a data step.

[DoumbiaS]

By null I mean 0 values. When I have 0 in a row of VALUE, the resulting VAR_WANT is equal to 0. I could also have missing values indeed.

I have access to proc iml even if i prefer alternative ways. Don't hesitate if you want to propose me a proc iml code.

Thanks.

[ballardw]

I don't have access to IML but I think if we call @Rick_SAS  he may have an idea.

[DoumbiaS]

To my knowledge, proc iml don't have a by statement, so it shouldn't be that easy with proc iml, hope not.

[ballardw]

---

@DoumbiaS wrote:

To my knowledge, proc iml don't have a by statement, so it shouldn't be that easy with proc iml, hope not.

---

Since you haven't provided any thing that is actually a data set I wouldn't jump to the conclusion that BY is even an option.

You appear to be selecting values to play with based on some sort of a diagonal not a group of records that BY would recognize. And that is one of the things that IML can do.

You may need to expound on some rules for identifying which rows make up which block of data. If the data can't tell the program what is needed than things are pretty problematic. There are lots of "problems" with and example of data I can create an output set that matches the "need" but is not likely to be extensible to a larger or more complex example.

[DoumbiaS]

The dataset is made up of VAR1, TIME and VALUE. By variables are VAR1 and TIME.

[ChrisNZ]

> By null I mean 0 values.

You mean nil then. null has a very different meaning.

[ChrisNZ]

Here is one way:

proc transpose out=TR;
  by VAR1; var VALUE;
run;
data PROD;
  set TR;
  PROD=coalesce(COL1,1)*coalesce(COL2,1)*coalesce(COL3,1)*coalesce(COL4,1);
run;

VAR1	_NAME_	COL1	COL2	COL3	COL4	PROD
1	VALUE	0.5	0.00	0.7	1	0.0000
2	VALUE	1.3	-0.94	0.4	.	-0.4888

[DoumbiaS]

As I have a lot of values of VAR1, proc transpose can be limited. In any case, this is my resulting VAR_WANT column values. I don't know if SAS can make products by going down.

0
0
0.7
1
-0.4888
-0.376
0.4

[ChrisNZ]

Just adapt the code. For example

data PROD;
  set TR;
  PROD=coalesce(COL1,1)*coalesce(COL2,1)*coalesce(COL3,1)*coalesce(COL4,1); 
  output;
  PROD=                 coalesce(COL2,1)*coalesce(COL3,1)*coalesce(COL4,1); 
  if COL2 ne . then output; 
  PROD=                                  coalesce(COL3,1)*coalesce(COL4,1); 
  if COL3 ne . then output; 
  PROD=                                                   coalesce(COL4,1);  
  if COL4 ne . then output; 
run;

VAR1	_NAME_	COL1	COL2	COL3	COL4	PROD
F1	VALUE	0.5	0.00	0.7	1	0.0000
F1	VALUE	0.5	0.00	0.7	1	0.0000
F1	VALUE	0.5	0.00	0.7	1	0.7000
F1	VALUE	0.5	0.00	0.7	1	1.0000
F2	VALUE	1.3	-0.94	0.4	.	-0.4888
F2	VALUE	1.3	-0.94	0.4	.	-0.3760
F2	VALUE	1.3	-0.94	0.4	.	0.4000

Another way if transposing presents issues could be by reading observations ahead. For example

data WANT ;
  merge HAVE         
        HAVE(firstobs=2 rename=(VAR1=VAR2 VALUE=V2))
        HAVE(firstobs=3 rename=(VAR1=VAR3 VALUE=V3))
        HAVE(firstobs=4 rename=(VAR1=VAR4 VALUE=V4));
  V2=ifn(VAR1=VAR2,V2,.);
  V3=ifn(VAR1=VAR3,V3,.);
  V4=ifn(VAR1=VAR4,V4,.);
run;

[ChrisNZ]

Or slightly more efficient:

data PROD;
  set TR;
  PROD=coalesce(COL1,1)*coalesce(COL2,1)*coalesce(COL3,1)*coalesce(COL4,1); output;
  if COL2=. then return;
  PROD=                 coalesce(COL2,1)*coalesce(COL3,1)*coalesce(COL4,1); output; 
  if COL3=. then return;
  PROD=                                  coalesce(COL3,1)*coalesce(COL4,1); output; 
  if COL4=. then return;
  PROD=                                                   coalesce(COL4,1); output; 
run;

[Ksharp]

Yes. That function cuprod() is under SAS/IML .
Why not write DATA STEP code to get it ?

[Rick_SAS]

It's not clear whether you have wide data or long data. For wide data, you can use PROC FCMP to define a product function that you can call from the DATA step. The same article mentions how to compute products for long data.

As others have mentioned, you can also do this in SAS/IML.

If you provide a DATA step with sample data, we can give more advice.

[Tom · Accepted solution]

Sounds like you need to use RETAIN.

data have ;
  input VAR1 :$32. TIME  VALUE  VAR_WANT $40.;
cards;
F1        0        .5      v1*v2*v3*v4 = 0.5*0*0.7*1
F1        1        0       v2*v3*v4 = 0*0.7*1
F1        2        .7      v3*v4 = 0.7*1
F1        3        1       v4 = 1
F2        0        1.3     w1*w2*w3 = 1.3*(-0.94)*0.4
F2        1        -.94    w2*w3 = (-0.94)*0.4
F2        2        .4      w3 = .4
;

proc sort data=have;
  by var1 descending time ;
run;

data want ;
  set have ;
  by var1 ;
  retain product ;
  if first.var1 then product=1;
  product=product*value;
run;

Results:

Obs    VAR1    TIME    VALUE    VAR_WANT                      product

 1      F1       3      1.00    v4 = 1                         1.0000
 2      F1       2      0.70    v3*v4 = 0.7*1                  0.7000
 3      F1       1      0.00    v2*v3*v4 = 0*0.7*1             0.0000
 4      F1       0      0.50    v1*v2*v3*v4 = 0.5*0*0.7*1      0.0000
 5      F2       2      0.40    w3 = .4                        0.4000
 6      F2       1     -0.94    w2*w3 = (-0.94)*0.4           -0.3760
 7      F2       0      1.30    w1*w2*w3 = 1.3*(-0.94)*0.4    -0.4888