I have a question I am using SAS 9.4 and enterprise guide 7.1. I have a dataset consisting of two months worth of data, summarized. See the enclosed excel workbook with a sample of some of the output. What I am trying to do is use the units and dollars from Nov as the denominator and the units and dollars from December as the numerator. So, I want the row number 11 compared to the row 2 units and dollars units: (38428 / 266324) dollars:(22211515.39 / 160519471.93). So this is basically looking at the number of accounts that moved from a lower bucket to a higher bucket. I would be doing the same calculation for 5 to 30, 30 to 60, 60 to 90, 90 to 120, 120 to 150, and 150 to 180. It is also important to use the correct cycle for the comparison, so cycle 1 November vs cycle 1 December. Cycle2 November to Cycle 2 December, cycle 3 to cycle 3 ex cetera.
You can write a PROC SQL to generate Cartesian product and then filter for your requirements. This is one way of approach, you can also so this kind of approach using a data step.
proc sql;
create table want as
select t1.*,
t2.PK as PK_,
t2.reportingdate as reportingdate_,
t2.cycle as cycle_,
t2.bucket1 as bucket1_,
t2.units1 as units1_,
t2.balance1 as balance1_,
t1.units1/t2.units1 as need1,
t1.balance1/t2.balance1 as need2
from have t1,have t2
where t1.cycle=t2.cycle and t1.reportingdate='17DEC2018'D
and t1.reportingdate<>t2.reportingdate
and ( ( t1.bucket1=5 and t2.bucket1=0)
or ( t1.bucket1=30 and t2.bucket1=5)
or ( t1.bucket1=60 and t2.bucket1=30)
or ( t1.bucket1=90 and t2.bucket1=60)
or ( t1.bucket1=120 and t2.bucket1=90)
or ( t1.bucket1=150 and t2.bucket1=120)
or ( t1.bucket1=180 and t2.bucket1=150)
)
order by t1.cycle,t1.reportingdate,t1.bucket1
;
quit;
Thanks Suryakiran. That is a reasonable solution.
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.