DATA Step, Macro, Functions and more

I want to subtract in one column values with previous value by groups

Reply
Contributor
Posts: 24

I want to subtract in one column values with previous value by groups

data event;
input ID event $ value;
cards;
1002 V1 1999
1002 V2 1985
1002 V3 1986
1003 V1 1999
1003 V2 1984
1003 V3 1983
1004 V1 2000
1004 V2 1999
1004 V3 1999
;

I want to substract only where ever the v2 and v3 values. for example:
if event = v3 and lag of event =v2 then val= v3-v2;

Frequent Contributor
Posts: 129

Re: I want to subtract in one column values with previous value by groups

[ Edited ]
Posted in reply to SrikanthY
DATA event;
   length lag1_event $2 lag1_value 8;
   set event;
   lag1_event=lag1(event);
   lag1_value=lag1(value);
   if event eq 'V3' and lag1_event eq 'V2' then val=value-lag1_value;
drop lag1:; RUN;

Cheers,
Oligolas

________________________

- Cheers -

Super User
Posts: 5,497

Re: I want to subtract in one column values with previous value by groups

Posted in reply to SrikanthY

While you now have a suggestion that works for the sample data you posted, I'm curious about one possibility.  Is it possible that you have a V2 record followed by a V3 record, but they belong to different IDs?  (If that's possible, I feel confident that the proposed suggestion could be modified to handle that case.)

Frequent Contributor
Posts: 129

Re: I want to subtract in one column values with previous value by groups

Posted in reply to Astounding

Good point.

If the IDs are relevant you need to check them as well.

DATA event;
   length lag1_event $2 lag1_value lag1_id 8;
   set event;
   lag1_event=lag1(event);
   lag1_value=lag1(value);
   lag1_id=lag1(id);
   if id eq lag1_id 
   and event eq 'V3' 
   and lag1_event eq 'V2' then val=value-lag1_value;
   drop lag1:;
RUN;
________________________

- Cheers -

Ask a Question
Discussion stats
  • 3 replies
  • 236 views
  • 0 likes
  • 3 in conversation