tom, this is magic. thank you so much! what if my data looks like the 1st table below and wanted to arrive at the 2nd table? i tried to apply ur logic but it is not working for some reason. it is spitting out 100s of score: score1, score2,.....  thank you! year name one_str_a one_str_b one_str_c one_con_a one_con_b one_con_c two_str_a two_str_b two_str_c two_con_a two_con_b two_con_c 1991 john 2 1 1 1 1 2 1 1 1 1 2 1 1991 amy 1 0 0 0 0 1 0 0 0 0 1 0 1991 ted 2 0 1 0 1 2 1 0 1 1 2 1 1991 tom 1 2 0 2 0 1 0 2 0 0 1 0 1992 john 0 1 0 1 0 0 0 1 0 0 0 0 1992 amy 0 2 2 2 2 0 2 2 2 2 0 2 1992 ted 2 1 1 1 1 2 1 1 1 1 2 1 1992 tom 1 2 2 2 2 1 2 2 2 2 1 2 varname Score 1991 john one_str_a 2 1991 john one_str_b 1 1991 john one_str_c 1 1991 john one_con_a 1 1991 john one_con_b 1 1991 john one_con_c 2 1991 john two_str_a 1 1991 john two_str_b 1 1991 john two_str_c 1 1991 john two_con_a 1 1991 john two_con_b 2 1991 john two_con_c 1 ... View more

I'm going to wait making any suggestion until aarony shows us what the data look like.  Given that it is a 12 character field I wouldn't assume to know what it looks like.  And aarony will need to convert it into a number and will be able to do it all within the original proc sql call. ... View more

data have; input date yymmdd8.; format date yymmddn8.; cards; 20040102 20040312 20040616 20041231 20060419 20061229 20080223 20081231 ; run; data last;   set have;   if date=mdy(1,1,year(date)+1)-1; run; Xia Keshan ... View more

Add in a where clause, 1950 to 1960 is an 11 year period though. proc sql; create table want as select companyid, year, count(*) as count_years from have where year between 1950 and 1960 group by companyid having count(year)>10; quit; ... View more

What is your definition of top 500?.  You sorted the file.  Do you want the first 500 in each sort group?  Do you want the top 500 sort groups in terms of frequency or percentage of the total?      A proc freq can tell you which sorted values are more frequent than others. ... View more