While @FreelanceReinh shows nicely how to do it in SQL, this can also be used as a nice illustration why one does not do it with SQL:
/* create some random test data of sufficient size */
data have;
format date yymmddd10.;
length cf $6;
date = '01jan2010'd;
do until (date > '31dec2019'd);
do i = 1 to 999;
cf = 'A' !! put(i,z5.);
amount = int(rand('uniform') * 10);
output;
end;
date = intnx('month',date,1,'b');
end;
drop i;
run;
/* SQL method */
proc sql;
create table want1 as
select a.date, a.cf, a.amount-b.amount as amount
from have a, have b
where a.cf=b.cf & intck('month',b.date,a.date)=1
order by date, cf;
quit;
/* data step method */
proc sort data=have;
by cf date;
run;
data want2;
set have;
by cf;
amount = dif(amount);
if not first.cf;
run;
proc sort data=want2;
by date cf;
run;
The log:
37 /* create some random test data of sufficient size */
38 data have;
39 format date yymmddd10.;
40 length cf $6;
41 date = '01jan2010'd;
42 do until (date > '31dec2019'd);
43 do i = 1 to 999;
44 cf = 'A' !! put(i,z5.);
45 amount = int(rand('uniform') * 10);
46 output;
47 end;
48 date = intnx('month',date,1,'b');
49 end;
50 drop i;
51 run;
NOTE: The data set WORK.HAVE has 119880 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 0.05 seconds
cpu time 0.02 seconds
52
53 /* SQL method */
54 proc sql;
55 create table want1 as
56 select a.date, a.cf, a.amount-b.amount as amount
57 from have a, have b
58 where a.cf=b.cf & intck('month',b.date,a.date)=1
59 order by date, cf;
NOTE: Table WORK.WANT1 created, with 118881 rows and 3 columns.
60 quit;
NOTE: PROZEDUR SQL used (Total process time):
real time 11.56 seconds
cpu time 3.88 seconds
61
62 /* data step method */
63 proc sort data=have;
64 by cf date;
65 run;
NOTE: There were 119880 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.HAVE has 119880 observations and 3 variables.
NOTE: PROZEDUR SORT used (Total process time):
real time 0.12 seconds
cpu time 0.03 seconds
66
67 data want2;
68 set have;
69 by cf;
70 amount = dif(amount);
71 if not first.cf;
72 run;
NOTE: There were 119880 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.WANT2 has 118881 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 0.07 seconds
cpu time 0.02 seconds
73
74 proc sort data=want2;
75 by date cf;
76 run;
NOTE: There were 118881 observations read from the data set WORK.WANT2.
NOTE: The data set WORK.WANT2 has 118881 observations and 3 variables.
NOTE: PROZEDUR SORT used (Total process time):
real time 0.09 seconds
cpu time 0.03 seconds
Note that while the combined 2 sorts and the data step consumed ~ 0.3 seconds, the SQL took close to 12(!) seconds, a multiple of 40!
Things like this gave me the reason to write down Maxim 10.
Also note that this dataset have is just 3MB(!!) in size; imagine running the SQL against a typical dataset that's in the range of several GBs, you would have a runtime counted in days or even weeks (because of the required sort and search operations, the time needed grows exponentially); the utility file built by the SQL is double the size of the input dataset, BTW.
PS thanks to @andreas_lds for the improved data step code.
