topic Re: Function prod in SAS in SAS Programming

Function prod in SAS

DoumbiaS — Wed, 02 Jun 2021 20:28:32 GMT

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

Re: Function prod in SAS

ballardw — Wed, 02 Jun 2021 21:05:42 GMT

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.

Re: Function prod in SAS

DoumbiaS — Wed, 02 Jun 2021 21:22:26 GMT

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.

Re: Function prod in SAS

ballardw — Wed, 02 Jun 2021 21:24:39 GMT

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

Re: Function prod in SAS

DoumbiaS — Wed, 02 Jun 2021 21:56:14 GMT

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

Re: Function prod in SAS

ChrisNZ — Wed, 02 Jun 2021 22:26:13 GMT

> By null I mean 0 values.

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

Re: Function prod in SAS

ballardw — Wed, 02 Jun 2021 22:36:14 GMT

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

Re: Function prod in SAS

ChrisNZ — Wed, 02 Jun 2021 22:44:42 GMT

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

Re: Function prod in SAS

DoumbiaS — Wed, 02 Jun 2021 23:26:59 GMT

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

Re: Function prod in SAS

DoumbiaS — Wed, 02 Jun 2021 23:38:58 GMT

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

Re: Function prod in SAS

ChrisNZ — Thu, 03 Jun 2021 00:34:22 GMT

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;

Re: Function prod in SAS

ChrisNZ — Thu, 03 Jun 2021 00:38:08 GMT

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;

Re: Function prod in SAS

Ksharp — Thu, 03 Jun 2021 12:53:28 GMT

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

Re: Function prod in SAS

Rick_SAS — Thu, 03 Jun 2021 14:50:41 GMT

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.

Re: Function prod in SAS

Tom — Thu, 03 Jun 2021 15:01:19 GMT

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

Re: Function prod in SAS

DoumbiaS — Mon, 07 Jun 2021 07:04:29 GMT

Yes absolutly, I did something like this too.

Thanks to all.