BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ujjawal
Quartz | Level 8

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

 

PCTCHNG.png

 

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

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.

 

View solution in original post

3 REPLIES 3
ballardw
Super User

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?

 

Astounding
PROC Star

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.

FreelanceReinh
Jade | Level 19

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.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1113 views
  • 1 like
  • 4 in conversation