data test; infile datalines; input studentID $ schoolID year score; datalines; a 123 2000 78 a 123 2000 78 a 123 2001 80 a 123 2001 80 a 123 2001 80 b 123 2002 95 b 456 2003 91 c 456 2003 90 c 456 2004 98 c 456 2006 92 ; run; proc sql; select *,(select mean(distinct score) from test where studentID=a.studentID and schoolID=a.schoolID) as mean from test as a; quit; ... View more

I tried this code but if there is a hospital in 2003 which is not in the other years, it gets overwritten. see this dataset;   data old; infile datalines; input hospid $ year ratio; datalines; A 2003 . A 2003 . B 2003 . C 2003 . A 2004 .2 B 2004 .3 B 2004 .3 D 2004 .4 A 2005 .3 ; run;   output should be this but instead C gets overwriten: A 2003 .208 A 2003 .208 B 2003 .312 C 2003 . A 2004 .2 B 2004 .3 B 2004 .3 D 2004 .4 A 2005 .3 ... View more

Show use some values that are character. I suspect that you have something such as NULL, NA, N/A or similar in the data somewhere and occuring often enough that the GUESSING elment of Proc import says it is character. If you know the first 5 rows are actually numeric in appearance them perhaps use Guessingrows=5 (or what ever number of rows at the top work) but his may cause issues with lengths of other character variables.   Or write a data step. The proc import code generated one and it should be in the log that you could copy, paste into the editor and modify the informat to be best6. or similar instead of the extemely likely $6. . ... View more