topic Re: Compute Industry Adjusted Variables in SAS Programming

Compute Industry Adjusted Variables

klndsy — Tue, 23 Feb 2016 20:57:48 GMT

Hello! I have a coding question for SAS 9.4.

I am trying adjust my quarterly ROA variable for each firm by industry means for each quarter. Thus, my end goal is to have IAROA= ROA - Industry Average for each quarter for each firm.

For a visual, I have a primary dataset that looks similar to this :

Firm Year.Quarter Industry ROA

1 2003.1 01 0.0125

1 2003.2 01 0.0359

1 2003.3 01 0.0451

1 2003.4 01 0.0215

2 2003.1 02 0.125

2 2003.2 02 0.135

2 2003.3 02 0.097

2 2003.4 02 0.102

3 2003.1 01 0.029

3 2003.2 01 0.024

3 2003.3 01 0.035

3 2003.4 01 0.028

... and so forth through 2014 and 75 industry types.

Then, I have found the industry ROA means for each quarter (2003-2014). using PROC MEANS. Specifically I use

proc means data=data.master mean noprint;
output out=IROA mean=;
by industry;
class yearquarter;
var roa ;

So, for example, I have:

Industry year.quarter Industry ROA (IROA)

01 2003.1 .0171

01 2003.2 .0172

01 2003.3 .0173

01 2003.4 .0174

01 2004.1

01 2004.2

01 2004.3

01 2004.4

02 2003.1 .111

02 2003.2 .112

02 2003.3 .113

02 2003.4 .114

02 2004.1

02 2004.2

02 2004.3

02 2004.4

... and so forth over all industries through 2014.

My end goal is to have:

Firm Year.Quarter Industry ROA IROA Industry Adjusted ROA

1 2003.1 01 0.0125 .0171 -.0046 (= .0125-.0171)

1 2003.2 01 0.0359 .0172 .0187 (= .0359-.0172)

1 2003.3 01 0.0451 .0173 .0278

1 2003.4 01 0.0215 .0174 .0041

2 2003.1 02 0.125 .111 .014

2 2003.2 02 0.135 .112 .023

2 2003.3 02 0.097 .113 -.016

2 2003.4 02 0.102 .114 -.012

3 2003.1 01 0.029 .0171 .0119

3 2003.2 01 0.024 .0172 .0068

3 2003.3 01 0.035 .0173 .0177

3 2003.4 01 0.028 .0174 .0106

As you can see, all firms will have the IROA value depending on what industry they belong to and the year.quarter of the observation. For example, Firms 1 and 3 belong to the same industry, so IROA takes on the same value for the same quarters.

Since I have thousand of firms, 75ish industries, and my data spans 11 years, it is not feasible for me to code this manually.

A) Is there a macro that works well?

B) I can find the quarterly industry means, but I have not been able to merge it back into the primary dataset so every firm with the matching industry and quarter display the correct value for the industry roa variable. Only the first firm receives the industry variable value an the rest of the firms have "."

Please let me know if there is any pertinent information missing that will allow you to help me with this code piece!

I thank you in advance!!!

Re: Compute Industry Adjusted Variables

Reeza — Tue, 23 Feb 2016 21:28:24 GMT

Post your code. You should be able to merge the results back in and then do your substraction in a data step.

I would recommend perhaps a SQL join and then you could even do the subtraction in one step. If you want, you can even calculate them all in one step.

Re: Compute Industry Adjusted Variables

klndsy — Tue, 23 Feb 2016 22:01:39 GMT

Hi Reeza; thank you for your reply.

So far, I have been trying this with annual ROA (ideally I would like quarterly), so my code reflects that. I figured once I get it figured out with annual, then I can apply it to my quarterly data. Additionally, sic2 is the industry variable.

I have done:

proc sort data=compustatrevised3; by sic2 year; run;
proc means data=compustatrevised3 mean noprint;
output out=IROA mean=;
by sic2;
class year;
var roa roa2;
run;

data IROA2;
set IROA (drop=_freq_);
if _type_=0 then delete;
rename roa=indroa roa2=indroa2;
run;

proc sort data=compustatrevised3; by sic2 year;
proc sort data=IROA2; by sic2 year; run;

data trialmerge;
merge compustatrevised3 IROA2;
by sic2 year;
run;

I have tried multiple variations of the merge command, but this is the one that gets me closest. However, it only fills the industry roa mean variable (indroa) for the first few firms of each year. Even then, I can't exactly figure out the problem because most of the time it will only fill the first firm, but sometimes it will fill 2, 3 or 4 firms with the industry roa value! I attached a screenshot (word doc) of my merged output problem.

Re: Compute Industry Adjusted Variables

Reeza — Tue, 23 Feb 2016 22:04:10 GMT

Is your primary data sample reflective of your actual data format/structure?

Re: Compute Industry Adjusted Variables

klndsy — Tue, 23 Feb 2016 23:47:27 GMT

Yes, but it is currently sorted differently and includes a lot more variables.

I have tens of thousands of firms spanning many different industries over a period of 2003-2014 with quarterly data. Each firm has a unique ROA each quarter (with some firms missing observations based on data availability).

My actual data set is sorted by industry(sic2) then year.quarter.

For a visual, my actual dataset looks similarly sorted to this (plus many other variables) :

Firm Year.Quarter Industry ROA

1 2003.1 01 0.0125

3 2003.1 01 0.029

1 2003.2 01 0.0359

3 2003.2 01 0.024

1 2003.3 01 0.0451

3 2003.3 01 0.035

1 2003.4 01 0.0215

3 2003.4 01 0.028

2 2003.1 02 0.125

2 2003.2 02 0.135

2 2003.3 02 0.097

2 2003.4 02 0.102

Re: Compute Industry Adjusted Variables

FreelanceReinh — Tue, 23 Feb 2016 23:55:42 GMT

Hi @klndsy,

I think, your code should work in principle (it could be simplified here and there, though) if the data are what they should be. The output dataset in your attached screenshot, however, does not look like the result of a merge by SIC2 YEAR, but rather like the result of a merge by SIC2 YQ (see obs. no. 1553 in particular!). One possible explanation is that some variable values shown in the screenshot are in fact formatted values. If, for example, variable YEAR contains (varying!) SAS date values (like those in YQ) which are only formatted with format YEAR., a merge by SIC2 YEAR could indeed yield results very similar to those shown in the screenshot.

Comparing YQ with QUARTER we see sort of an inconsistency which could impact your quarterly results as well: Variable YQ contains SAS date values, but sometimes they are varying within one quarter: YQ=15764='28FEB2003'd in obs. no. 1553 and YQ=15795='31MAR2003'd in obs. no. 1554 belong to the same quarter, but only a variable with a constant value (such as '2003.1') throughout a quarter would be suitable for merging by quarter. Again, it is not clear if variable QUARTER contains values such as 2003.1 (numeric or character?) or if it contains SAS date values, formatted with format YYQP..

I think, once this issue is clarified, you are very close to getting the intended results.

Just to suggest an alternative approach: You could let PROC STDIZE compute both the IROA values and the differences ROA - IROA. Please find below some sample code. However, you are so close to the finish line with your more elementary approach that you may want to continue on this route.

/* Create test data */

data have;
input firm yearquarter industry $ ROA;
cards;
1 2003.1 01 0.0125
1 2003.2 01 0.0359
1 2003.3 01 0.0451
1 2003.4 01 0.0215
2 2003.1 02 0.125
2 2003.2 02 0.135
2 2003.3 02 0.097
2 2003.4 02 0.102
3 2003.1 01 0.029
3 2003.2 01 0.024
3 2003.3 01 0.035
3 2003.4 01 0.028 
;

/* Sort input data and perform computations */

proc sort data=have;
by industry yearquarter;
run;

proc stdize data=have method=mean oprefix sprefix=IndAdj
            out=have_adj outstat=stats(where=(_type_='LOCATION') rename=(ROA=IROA));
var ROA;
by industry yearquarter;
run;

/* Merge firm-quarter level with industry-quarter level data and restore original sort order */

data want;
merge have_adj
      stats(drop=_type_);
by industry yearquarter;
label IndAdjROA='Industry Adjusted ROA';
run;

proc sort data=want;
by firm yearquarter;
run;

I'll be back tomorrow (CET).

Re: Compute Industry Adjusted Variables

klndsy — Wed, 24 Feb 2016 01:09:58 GMT

Hi FreelandReinhard! Thank you for your time! Yes, my variables Year and YQ are both formatted variables from datadate which has the format 01012003. I thought I took care of this using put and input, but clearly I did not! I have exhausted everything I can think of with this issue. Do you have any suggestions???

Re: Compute Industry Adjusted Variables

AskoLötjönen — Wed, 24 Feb 2016 10:16:16 GMT

You can do all calculations with one Proc SQL: Industry average ROA are calculated in subquery:

proc sql;

create table want as

select P.Firm,

P.YearQuarter,

P.Industry,

P.ROA,

I.IROA,

(P.roa-I.IROA) as IAROA

from primary P

left join (select Sub.Industry, Sub.YearQuarter, mean(Sub.ROA) as IROA from Primary Sub group by Sub.Industry, Sub.YearQuarter) I

on P.Industry = I.Industry

and P.YearQuarter = I.YearQuarter

order p.Firm, P.YearQuarter;

quit;

Re: Compute Industry Adjusted Variables

FreelanceReinh — Wed, 24 Feb 2016 10:16:23 GMT

I think, the easiest and safest way to prepare the aggregations and merges by year and by quarter is to create variables which contain year and quarter (incl. year), respectively.

Example:

/* Sample data */
data old;
date='24FEB2016'd; /* numeric variable with values like 20508, possibly derived */
run;               /* from a raw date value like rawdate='24022016' by using    */
                   /* the INPUT function: date=input(rawdate, ddmmyy8.)         */

/* To be created */
data new;
set old;
year=year(date); /* numeric variable with values like 2016 */
quarter=put(date, yyqp.); /* character variable with values like '2016.1' */
run;

You say that YQ is formatted. My impression was rather that YQ is unformatted (screenshot shows SAS date values), but QUARTER could be formatted.

Re: Compute Industry Adjusted Variables

FreelanceReinh — Wed, 24 Feb 2016 10:41:24 GMT

@AskoLötjönen: Sure, this would be the most straightforward approach. It just seemed that the OP is more familiar with the data step and PROC MEANS.

Re: Compute Industry Adjusted Variables

klndsy — Wed, 24 Feb 2016 15:11:22 GMT

Y'all were so wonderful and helpful. It is all working beautifully now. Thank you very much!