topic Re: Find difference for each group in sas in SAS Procedures

Find difference for each group in sas

user24 — Wed, 19 Aug 2015 13:50:16 GMT

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

Re: Find difference for each group in sas

Reeza — Wed, 19 Aug 2015 14:42:28 GMT

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;

Re: Find difference for each group in sas

Steelers_In_DC — Wed, 19 Aug 2015 14:49:21 GMT

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

Re: Find difference for each group in sas

MikeZdeb — Wed, 19 Aug 2015 15:00:33 GMT

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

Re: Find difference for each group in sas

MikeZdeb — Wed, 19 Aug 2015 19:20:59 GMT

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;

Re: Find difference for each group in sas

Ksharp — Fri, 21 Aug 2015 06:31:51 GMT

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