topic Re: Getting yearly average of each firm. in SAS Programming

Getting yearly average of each firm.

JKCho — Thu, 18 Jul 2019 21:47:50 GMT

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.

Re: Getting yearly average of each firm.

Reeza — Thu, 18 Jul 2019 22:30:23 GMT

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.

Re: Getting yearly average of each firm.

JKCho — Thu, 18 Jul 2019 22:34:21 GMT

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

Re: Getting yearly average of each firm.

JKCho — Thu, 18 Jul 2019 23:16:30 GMT

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?

Re: Getting yearly average of each firm.

Reeza — Fri, 19 Jul 2019 00:23:13 GMT

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)

Re: Getting yearly average of each firm.

JKCho — Fri, 19 Jul 2019 15:14:37 GMT

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