turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Base SAS manipulating calculation using different ...

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-26-2018 08:45 PM

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) dollars22211515.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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to pangea17

03-26-2018 10:20 PM

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

Suryakiran

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to SuryaKiran

03-27-2018 11:40 AM

Thanks Suryakiran. That is a reasonable solution.