PROC FREQ

[FEFE90]

Hi,

Can anyone help me solve this question? I am not sure where I am mistaken.

Q : HOW MANY FIRM HAVING AVERAGE COMMON EQUITY/TOTAL ASSET RATIO GREATER THAN 50%? 

data TOPCOM_TIC; set Q_COMP_SHORT10_18;
keep TIC CEQQ ATQ COM_EQ;
COM_EQ = CEQQ/ATQ;
run;

proc sort data=TOPCOM_TIC ;  
BY descending TIC COM_EQ;
RUN; 

proc means data=TOPCOM_TIC;
var COM_EQ ; BY TIC ;
output out=avg_CE
mean=avg_CE_TIC;
run;

Proc freq data=TOPCOM_TIC;
by COM_EQ ;
table TIC /out=Q6;
run;

data Q6; set Q6;
if COM_EQ>=50 ;
run;

Thank you

[Norman21]

COM_EQ is a simple ratio, not a percentage.

Try changing the penultimate line from

if COM_EQ>=50 ;

to

if COM_EQ>=0.5 ;

[FEFE90]

I changed to 0.5 but I think something is incorrect with the proc means code. Any idea?

[Norman21]

Have you inspected the data files? Are any of the values greater than 0.5?

[FEFE90]

yes, there are a few that greater than 0.5

[Norman21]

In Q6?

[FEFE90]

the code is not running from proc means, so the ones after that show errors. Should I change it from Q6 to TOPCOM_TIC. 

Thank you

[mkeintz · Accepted solution]

What do you mean by "not running"?   I.e., what is the sas log telling you?  If we could see the log, we might be able to provide more effective assistance.

However, to see if I understand the object:  you want to calculate for each firm (variable TIC), the average ratio of quarterly reports of common equity (CEQ) to total assets (ATQ), right?   It looks to me like you are using COMPUSTAT data.

To do this you, don't need to sort - you can use the CLASS statement in proc means to get averages for each TIC:

data TOPCOM_TIC; set Q_COMP_SHORT10_18;
  keep TIC CEQQ ATQ COM_EQ;
  COM_EQ = CEQQ/ATQ;
run;

proc means data=topcom_tic noprint nway;
  class tic;
  var com_eq;
  output out=stats (where=(_stat_='MEAN'));
run;

data need;
  set stats;
  over_under=sign(com_eq-.5);
run;

proc freq data=need;
  tables over_under;
run;

1. The PROC MEANS has an NWAYS option.  Otherwise the output dataset would include not only the averages for each TIC ("one way" results), but also the global average ("zero way").  NWAY says to generate only the highest combination of CLASS variables  (for instance, if you had "CLASS SIC COUNTRY;", you would get only the two-way means for all SIC*COUNTRY combinations.).
2. The data need step just makes a variable with +1 for ratio over .5, and -1 for ration < .5.  (exactly .5 leaves COM_EQ as a missing, so perhaps you would prefer    "OVER_NOTOVER= (com_eq>= 0.5);"

[FEFE90]

Thank you