BookmarkSubscribeRSS Feed
user24
Obsidian | Level 7

I have a table as below

Header 1Header 2Header 3Header 4Header 5
adGrad2015O25
adGrad2014O20
admsHome2015O55
admsHome2014O40
admsNot2015T70
admsNot2014T15

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 1Header 2Header 3Header 4Header 5
adGrad2015-2014(difference)O5
admsHome2015-2014(difference)O

15

admsNot2015-2014(difference)T55
5 REPLIES 5
Reeza
Super User

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
Barite | Level 11

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
Rhodochrosite | Level 12

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
Rhodochrosite | Level 12

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
Super User

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

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1119 views
  • 2 likes
  • 5 in conversation