Getting yearly average of each firm.

[JKCho]

data credit1;
set credit;
format datadate year.;
rename datadate = year;
run;

proc sql;
create table credtY as 
 select cusip, year, mean(rating) as ratingY 
  from credit1
   group by cusip, year;
   quit;

Hi! 

There is no error or warning sign on the above coding... So I think the code itself works but I need sth else to get what I want.

I ran the code to get yearly average ratings(ratingY) for each frim of each year.

However, what I get is the same monthly ratings as the previous data file is.

I changed YYMMDD date to Year and use the code. 

Assuming the reason why I couldn't obtain the desired outcome is 1) many duplicates or 2) Year is not really a year but YYMMDD... 

To resolve this issue, I used "nodupkey" to delete duplicates but failed to delete them at all. 

I would like to ask how can I can yearly average of each firm when I have seemingly duplicate Years BUT possibly YYMMDD.

[Reeza · Accepted solution]

SQL doesn't use the formatted value when aggregating, it uses the underlying values. However if you use PROC MEANS or any other PROC it will use the formatted values.

[JKCho]

Yeah... I passed the first hurdle. But... if my YEARS are based on underlying values, how can I merge later? I need to use YEAR to match... Can I use input() statement to convert my YEAR to a PURE year?

If not... is there any ways to use this YEAR with underlying values for matching with normal pure years?

[Reeza]

If you're joining in SQL you can actually join with a function.

select *
from x
left join y
on year(x.date) = year(y.date)

[JKCho]

Thank you Reeza,
This is good! I will apply this code now and later!

[JKCho]

Thank you Reeza!!!
I rather use proc means so!