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

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

SrikanthY — Mon, 24 Oct 2016 13:59:52 GMT

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;

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

Oligolas — Mon, 24 Oct 2016 14:16:21 GMT

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

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

Astounding — Mon, 24 Oct 2016 14:44:18 GMT

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.)

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

Oligolas — Mon, 24 Oct 2016 15:17:43 GMT

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;