DATA Step, Macro, Functions and more

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

Reply
Occasional Contributor
Posts: 17

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: 104

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

[ Edited ]
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,099

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

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: 104

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

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
  • 229 views
  • 0 likes
  • 3 in conversation