topic Re: Product function in SAS Programming

Product function

Jorge_Silva — Mon, 29 Mar 2021 10:31:23 GMT

Hello.

I'm trying to replicate the sample data below in Excel (Period + Have) to SAS, trying to calculate the product between periods (for period 1 = have1; for period 2 = have 1*have2; for period 3 = have1*have2*have3; etc.) or alternatively multiply the current period by the previous period (for period 1 = have1; for period 2 = have2*Or1; for period 3 = have 3*Or2; etc).

I've already tried with loops or with the product workaround available but I'm not getting the results I desire.

Is there someone who can help me please?

Many thanks.

Excel Sample:

Period	Have	Want	Excel Formula	Or	Excel Formula
1	0,98373490	0,98373490		0,98373490	=+B2
2	0,95077541	0,93531096	=PRODUCT(B$2:B3)	0,93531096	=+B3*E2
3	0,92290404	0,86320226	=PRODUCT(B$2:B4)	0,86320226	=+B4*E3
4	0,93952522	0,81100029	=PRODUCT(B$2:B5)	0,81100029	=+B5*E4
5	0,96857611	0,78551551	=PRODUCT(B$2:B6)	0,78551551	=+B6*E5

Re: Product function

Astounding — Mon, 29 Mar 2021 12:56:12 GMT

If I understand the problem correctly, SAS does this pretty easily:

data new;
   set old (keep=period have);
   retain want 1;
   want = want * have;
run;

When moving from Excel to SAS, you need to get used to the idea that SAS is processing a single row at a time.

Re: Product function

Kurt_Bremser — Mon, 29 Mar 2021 11:32:13 GMT

data want;
set have;
retain want;
if _n_ = 1
then want = have;
else want = want * have;
run;

Re: Product function

Jorge_Silva — Tue, 30 Mar 2021 10:37:08 GMT

Hi.
Thanks a lot to both for your very quick answers. Both solutions work perfectly and solve my problem.
Best regards.

Re: Product function

Rick_SAS — Mon, 27 Dec 2021 13:40:32 GMT

I used PROC FCMP to implement a PRODUCT function. The implementation also handles missing values. See "Implement a product function in SAS."