topic Re: How to create this variable in SAS Programming

How to create this variable

jony123 — Wed, 15 Dec 2021 13:58:00 GMT

Hi experts,

I have this table:

quarter	pse	psn
q1	0.976	0.77
q2	0.987	0.76
q3	0.56	0.954
q4	0.77	0.876

i need to create roll up measure form each variable (pse,psn) like the example below:

quarter	pse	psn	pse_clac	psn_clac
q1	0.976	0.77	10.84133	11.4194
q2	0.987	0.76	5.486504	6.451639
q3	0.56	0.954	2.7612	3.665704
q4	0.77	0.876	1.77	1.876

pse_calc for q1= (1+0.876)*(1+9.54)*(1+0.76)*(1+0.77)

q2= (1+0.876)*(1+9.54)*(1+0.76)

q3=(1+0.876)*(1+9.54)

q4=(1+0.876)

Thanks for your help

Re: How to create this variable

PaigeMiller — Wed, 15 Dec 2021 14:30:43 GMT

/* UNTESTED CODE */
proc sort data=have;
    by descending quarter;
run;
data want;
    set have;
    retain pse_calc 1;
    pse_calc=pse_calc*(1+pse);
run;
proc sort data=want;
    by quarter;
run;

If you want tested code, please provide the data as SAS data step code (instructions).

Re: How to create this variable

ballardw — Wed, 15 Dec 2021 15:53:18 GMT

You really should state your rules. This appears to use values of other rows however you show (1+9.54) when the apparent variable value you show is .954. So why would only the .954 by multiplied by 10 when none of the other values are?

And since you show nothing using the PSE variable does it have a similar exception?

Re: How to create this variable

PaigeMiller — Wed, 15 Dec 2021 16:10:18 GMT

@ballardw wrote:

You really should state your rules. This appears to use values of other rows however you show (1+9.54) when the apparent variable value you show is .954. So why would only the .954 by multiplied by 10 when none of the other values are?

And since you show nothing using the PSE variable does it have a similar exception?

These are good points. Also, in this code

pse_calc for q1= (1+0.876)*(1+9.54)*(1+0.76)*(1+0.77)
                   q2= (1+0.876)*(1+9.54)*(1+0.76)
                  q3=(1+0.876)*(1+9.54)
                 q4=(1+0.876)

the value being calculated is called pse_calc but the numbers used are from the psn variable. @jony123 you need to clear up these issues.

Re: How to create this variable

Tom — Wed, 15 Dec 2021 16:30:00 GMT

Given those furmula you need to do the calculations in reverse chronological order. So sort the data with DESCENDING option.

data have;
  input quarter $ pse psn pse_clac psn_clac ;
cards;
q1 0.976 0.77 10.84133 11.4194
q2 0.987 0.76 5.486504 6.451639
q3 0.56 0.954 2.7612 3.665704
q4 0.77 0.876 1.77 1.876
;

proc sort;
  by descending quarter;
run;

data want;
  set have;
  by descending quarter;
  retain pse_new 1 psn_new 1;
  pse_new=pse_new*(1+pse);
  psn_new=psn_new*(1+psn);
run;

proc print;
run;

Obs    quarter     pse      psn     pse_clac    psn_clac    pse_new    psn_new

 1       q4       0.770    0.876      1.7700      1.8760     1.7700     1.8760
 2       q3       0.560    0.954      2.7612      3.6657     2.7612     3.6657
 3       q2       0.987    0.760      5.4865      6.4516     5.4865     6.4516
 4       q1       0.976    0.770     10.8413     11.4194    10.8413    11.4194

Re: How to create this variable

Ksharp — Thu, 16 Dec 2021 12:17:26 GMT

data have;
  input quarter $ pse psn ;
id+1;
cards;
q1 0.976 0.77 
q2 0.987 0.76 
q3 0.56 0.954 
q4 0.77 0.876 
;

proc sql;
create table want as
select distinct a.*,
 10**sum(log10(1+b.pse)) as pse_clac ,
 10**sum(log10(1+b.psn)) as psn_clac
 from have as a,have as b
  where a.id<=b.id
   group by a.id;
quit;