topic Re: PROC FREQ in New SAS User

PROC FREQ

FEFE90 — Sun, 13 Oct 2019 12:21:14 GMT

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

Re: PROC FREQ

Norman21 — Sun, 13 Oct 2019 13:16:19 GMT

COM_EQ is a simple ratio, not a percentage.

Try changing the penultimate line from

if COM_EQ>=50 ;

if COM_EQ>=0.5 ;

Re: PROC FREQ

FEFE90 — Sun, 13 Oct 2019 13:22:56 GMT

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

Re: PROC FREQ

Norman21 — Sun, 13 Oct 2019 13:26:58 GMT

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

Re: PROC FREQ

FEFE90 — Sun, 13 Oct 2019 13:29:08 GMT

yes, there are a few that greater than 0.5

Re: PROC FREQ

Norman21 — Sun, 13 Oct 2019 13:48:25 GMT

In Q6?

Re: PROC FREQ

FEFE90 — Sun, 13 Oct 2019 13:52:28 GMT

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

Re: PROC FREQ

mkeintz — Sun, 13 Oct 2019 15:51:48 GMT

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;

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.).
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);"

Re: PROC FREQ

FEFE90 — Sun, 13 Oct 2019 16:42:07 GMT

Thank you