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
/* 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).
/* 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).
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?
@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.
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.