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
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;
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.
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.
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;
Thank you so much for your help, Richard.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.