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
- /
- Refer the non-blank column

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
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-07-2016 01:20 PM

I am trying to calculate the percentage change between the values of first and last updated column.

For eg. For the first row, it should be (89-85)/85. Second row - (74-79)/79 and so on.

To solve it, i have transposed the data set and generate serial number against IDs and then pick variables based on the highest and smallest serial numbers to calculate % change. It seems i make it complex. There should be a simple way to calculate it.

```
data temp;
input ID x1-x4;
cards;
1 85 89 . .
2 79 75 74 .
3 80 82 86 85
;
run;
proc transpose data = temp out = temp2;
by ID;
var x1-x4;
run;
data temp3;
set temp2 (where = (COL1 ^= .));
retain Serial 0;
If first.ID then Serial = 1;
else Serial = Serial + 1;
by ID;
run;
proc sql noprint;
create table t2 as
select a.ID, a.COL1, b.COL1 as COL2, (a.COL1-b.COL1)/b.COL1 *100 as change
from temp3 a left join
(select ID, COL1 from temp3
group by ID
having serial = min(serial)) b
on a.ID = b.ID
group by a.ID
having serial = max(a.serial);
create table t3 as
select * from temp a left join t2 b
on a.ID = b.ID;
quit;
```

Accepted Solutions

Solution

03-20-2016
06:55 AM

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

03-07-2016 03:43 PM

Or still a bit shorter:

```
data want;
set temp;
pct_chng=coalesce(x4,x3,x2,x1)/x1-1;
run;
```

However, your PROC SQL approach would produce different results for observations with, say, x1=. and x2>., namely the change from the *first non-missing* to the last non-missing value in (x1, x2, x3, x4). If this is what you want, you could define

`pct_chng=coalesce(x4,x3,x2,x1)/coalesce(of x1-x4)-1;`

Either way, of course, you should be sure that the denominator cannot be zero or add code to handle this case.

All Replies

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

03-07-2016 01:33 PM

The simple change you mention in the text is this:

```
data temp;
input ID x1-x4;
pct_chng = (x2-x1)/x1;/* or multiply by 100 if you really need to but a Percent format may be more appropriate*/
cards;
1 85 89 . .
2 79 75 74 .
3 80 82 86 85
;
run;
```

I'm not sure, but were you looking to find the smallest of changes if using x2, x3 and x4?

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

03-07-2016 01:40 PM

I think this sort of simplification is entirely the right approach. But I think your problem is marginally more complex:

lastval=x2;

if x3 > . then lastval=x3;

if x4 > . then lastval=x4;

pct_change = (lastval - x1) / x1;

It's still a one-step process though.

Solution

03-20-2016
06:55 AM

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

03-07-2016 03:43 PM

Or still a bit shorter:

```
data want;
set temp;
pct_chng=coalesce(x4,x3,x2,x1)/x1-1;
run;
```

However, your PROC SQL approach would produce different results for observations with, say, x1=. and x2>., namely the change from the *first non-missing* to the last non-missing value in (x1, x2, x3, x4). If this is what you want, you could define

`pct_chng=coalesce(x4,x3,x2,x1)/coalesce(of x1-x4)-1;`

Either way, of course, you should be sure that the denominator cannot be zero or add code to handle this case.