Find difference for each group in sas

[user24]

I have a table as below

Header 1	Header 2	Header 3	Header 4	Header 5
ad	Grad	2015	O	25
ad	Grad	2014	O	20
adms	Home	2015	O	55
adms	Home	2014	O	40
adms	Not	2015	T	70
adms	Not	2014	T	15

I need to find difference year for each group. It is just a small example. So what i need to add 3 more row as below

Header 1	Header 2	Header 3	Header 4	Header 5
ad	Grad	2015-2014(difference)	O	5
adms	Home	2015-2014(difference)	O	15
adms	Not	2015-2014(difference)	T	55

[Reeza]

If you always have two observations the following should work. My guess would be the problem is more complicated though...

proc sort data=have; by header1 header2 descending header3;

run;

data want;

set have;

by header1 header2 descending header3;

header3_lag=lag(header3);

header3_diff=catx("-", header3, header3_lag);

header5_Diff=dif(header5);

if last.header2 then output;

run;

[Steelers_In_DC]

data have;

infile cards dsd;

input header1$ header2$ header3 header4$ header5;

cards;

ad,grad,2015,O,25

ad,grad,2014,O,20

adms,home,2015,O,55

adms,home,2014,O,40

adms,not,2015,T,70

adms,not,2014,T,15

;

proc sort data=have;by header1 header2 descending header3;

data prep;

set have;

diff_year = lag(header5);

by header1 header2 descending header3 notsorted;

if not first.header2 then do;

    difference = diff_year-header5;

end;

run;

proc sql;

create table want as

select distinct

header1,

header2,

header3,

header4,

difference as header5

from prep

having not missing(header5);

[MikeZdeb]

Hi.  Another idea ...

data x;

input (header1-header3) (:$4.) header4 :$1. header5;

datalines;

ad Grad 2015 O 25

ad Grad 2014 O 20

adms Home 2015 O 55

adms Home 2014 O 40

adms Not 2015 T 70

adms Not 2014 T 15

;

data y;

* just to keep variables in header1 through header5 order;

retain header1-header5;

length header3 $22;

merge x(where=(header3 eq '2015')) x(where=(header3 eq '2014') rename=(header5=_n_));

by header1 header2;

header5 = header5 - _n_;

header3 = '2015-2014(difference)';

run;

DATA SET Y ...

Obs    header1    header2           header3           header4    header5

1      ad         Grad      2015-2014(difference)       O           5

2      adms       Home      2015-2014(difference)       O          15

3      adms       Not       2015-2014(difference)       T          55

[MikeZdeb]

Hi, one more idea ...

data y;

retain header3 '2015-2014(difference)';

do j=1 to howmany;

   set x (drop=header3) nobs=howmany point=j;

   j+1;

   set x (drop=header3 rename=(header5=_n_)) point=j;

   header5 = header5 - _n_;

   output;

end;

stop;

run;

[Ksharp]

There are just two obs for each group or MORE ?

data have;
infile cards dsd;
input header1$ header2$ header3 header4$ header5;
cards;
ad,grad,2015,O,25
ad,grad,2014,O,20
adms,home,2015,O,55
adms,home,2014,O,40
adms,not,2015,T,70
adms,not,2014,T,15
;
run;

data want;
 set have;
 by header1 header2;
 retain first;
 if first.header2 then do;first=header3;sum=header5;end;
  else sum+-header5;
 if last.header2 then do;_header3=catx('-',first,header3);header5=sum;output;end;
 drop header3  first sum;
run

Xia Keshan