topic Re: Difference computation between quarters in the same company in SAS Programming

Difference computation between quarters in the same company

joon1 — Thu, 03 Sep 2020 04:39:04 GMT

Dear Madam/Sir,

I tried to compute differences between quarters in the same company using the following code, but it did not work.

GVKEY

DATADATE

FYEARQ

FQTR

saley

1004	19850831	1985	1	53.836
1004	19851130	1985	2	61.04
1004	19860228	1985	3	64.541
1004	19860531	1985	4	68.595
1004	19860831	1986	1	66.376
1004	19861130	1986	2	75.907
1004	19870228	1986	3	71.83
1004	19870531	1986	4	84.079
1004	19870831	1987	1	74.543
1004	19871130	1987	2	81.31
1004	19880229	1987	3	92.951
1004	19880531	1987	4	100.858
1004	19880831	1988	1	90.007
1004	19881130	1988	2	100.645
1004	19890228	1988	3	102.318
1004	19890531	1988	4	113.39

1st Quarter saleq = 1st Quarter saley
2nd Quarter saleq = 2nd Quarter saley - 1st Quarter saley
3rd Quarter saleq = 3rd Quarter saley - 2nd Quarter saley
4th Quarter saleq = 4th Quarter saley- 3rd Quarter saley

data a5; set a4;
by fyearq;
if fqtr=1 then cf1=saley;
if fqtr=2 then cf2=saley;
if fqtr=3 then cf3=saley;
if fqtr=4 then cf4=saley;
if fqtr=2 then saleq=cf2-cf1;
if fqtr=3 then saleq=cf3-cf2;
if fqtr=4 then saleq=cf4-cf3;
run;

Any help will be highly appreciated.

Thanks

Joon1

Re: Difference computation between quarters in the same company

mkeintz — Thu, 03 Sep 2020 05:09:08 GMT

Use the DIF function, defined as DIF(x)=X-LAG(X):

data a5; 
  set a4;
  saleq=dif(saley);
  if fqtr=1 then saleq=saley;
run;

BTW, this code requires that each GVKEY begins with FQTR=1. Otherwise the first record of an offending GVKEY would have values contaminated with a lagged saley value from the prior gvkey.

Edit note: Of course this also assumes data are sorted by gvkey/datadate. And that there are no missing quarters.

Re: Difference computation between quarters in the same company

andreas_lds — Thu, 03 Sep 2020 05:33:42 GMT

Please post the data in usable form, being a member of the community for three years, you should know that excel-files are not usable for many other members. Also add what you expect as result using the data provided.

Re: Difference computation between quarters in the same company

RichardDeVen — Thu, 03 Sep 2020 10:34:10 GMT

Try DIF with special handling of FIRST.<company> Coding an IFN() function call ensures all arguments that are expressions will be evaluated and thus you don't need to worry about a conditional DIF() functional call that would compute difference from the wrong lag.

data want;
  set have;
  by gvkey fyearq fqtr;  * only need gvkey, but other two ensures ERROR occurs if data is NOT sorted for quarter to quarter computation;
  sale_qdif = ifn(first.gvkey, ., dif1(saley));
run;

Re: Difference computation between quarters in the same company

joon1 — Thu, 03 Sep 2020 22:53:39 GMT

Thank you so much for your help, Richard.