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
- /
- ODS and Base Reporting
- /
- Calculating a relative value using proc report

Topic Options

- Subscribe to 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
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-04-2008 04:20 AM

Hi

I have a situation whereby i need to calculate a value for each row, relative to the first row.

ie. if i have the following table

a b c

d e f

g h i

then i wish to calculate

a b c/c

d e f/c

g h i/c

I have been able to do this using data steps and tabulates however because i repeat the calculation many times it is very time consuming and computationally demanding.

I have also been able to calculate the value for consecutive rows, but cannot keep dividing by the value c.

Any help would be greatly appreciated.

Chis

I have a situation whereby i need to calculate a value for each row, relative to the first row.

ie. if i have the following table

a b c

d e f

g h i

then i wish to calculate

a b c/c

d e f/c

g h i/c

I have been able to do this using data steps and tabulates however because i repeat the calculation many times it is very time consuming and computationally demanding.

I have also been able to calculate the value for consecutive rows, but cannot keep dividing by the value c.

Any help would be greatly appreciated.

Chis

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

Posted in reply to deleted_user

06-04-2008 08:35 AM

Does this work for you? It assumes that z will never be missing.

[pre]

data;

input x y z;

datalines;

1 2 3

4 5 6

7 8 9

;;;;

proc report nowd;

col x y z z2;

define x--y / display;

define z / display noprint;

define z2 / computed;

compute z2;

if keep = . then keep = z;

z2 = z / keep;

endcomp;

run;[/pre]

Produces

[pre]

x y z2

1 2 1

4 5 2

7 8 3

[/pre]

[pre]

data;

input x y z;

datalines;

1 2 3

4 5 6

7 8 9

;;;;

proc report nowd;

col x y z z2;

define x--y / display;

define z / display noprint;

define z2 / computed;

compute z2;

if keep = . then keep = z;

z2 = z / keep;

endcomp;

run;[/pre]

Produces

[pre]

x y z2

1 2 1

4 5 2

7 8 3

[/pre]

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

Posted in reply to deleted_user

06-04-2008 11:57 AM

That's a fantastic solution, excellent bit of code.

Thanks,

Chris

Thanks,

Chris

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

Posted in reply to deleted_user

06-06-2008 08:47 AM

Hi again,

I've used the bit of code supplied ealier and it has worked well, however i have been unable to adapt it to deal with the following problem:

If i have a report that is grouped by month, i need the relative figure to be figured out independantly for each month.

ie. if i have

month x y z

Jan 1 2 3

Jan 4 5 6

Jan 7 8 9

Feb a d c

Feb d e f

Feb h i j

I need to calculate

Jan 1 2 3 3/3

Jan 4 5 6 6/3

Jan 7 8 9 9/3

Feb a b c c/c

Feb d e f f/c

Feb g h i i/c

Any help would be greatly appreciated.

Thanks,

Chris

I've used the bit of code supplied ealier and it has worked well, however i have been unable to adapt it to deal with the following problem:

If i have a report that is grouped by month, i need the relative figure to be figured out independantly for each month.

ie. if i have

month x y z

Jan 1 2 3

Jan 4 5 6

Jan 7 8 9

Feb a d c

Feb d e f

Feb h i j

I need to calculate

Jan 1 2 3 3/3

Jan 4 5 6 6/3

Jan 7 8 9 9/3

Feb a b c c/c

Feb d e f f/c

Feb g h i i/c

Any help would be greatly appreciated.

Thanks,

Chris