Dear SAS community, I have a panel dataset that has individuals (people), companies and year-quarter variables as identifier. For each individual I would like to compute the average value of var for each company for the respective year-quarter but excluding the value of that individual in the average. Basically I would like to compute: DIFFijt=[VARijt-average(VARjt(-i))]/ average(VARjt(-i)) where i is the individual, j is the company identifier and t is the year-quarter time identifier. For example imagine this individual is a CEO and I would like to compare him with all other CEOs. So the average should exclude the CEO i.   Here is the sample code that includes the individual in the average. How do I change this code? proc sql; create table average_var as select avg(var) as avg_var, companyid, qtr from have group by companyid, qtr; create table diff as select a.*, avg_var from have a left join average_var b on a.companyid=b.companyid and a.qtr=b.qtr; quit;   data have; infile datalines dsd truncover; input individual:Z6. companyid:32. qtr:$6. var:32.; format individual Z6.; datalines4; 147283,14392,2013Q4,0.14 534751,14392,2013Q4,0.13 537668,14392,2013Q4,0.03 545356,14392,2013Q4,0.47 561697,14392,2013Q4,0.14 571781,14392,2013Q4,0.02 575241,14392,2013Q4,0 654072,14392,2013Q4,0.07 055939,14378,2013Q4,0.82 507535,14378,2013Q4,0.59 581570,14378,2013Q4,1.02 614746,14378,2013Q4,1.03 146328,14423,2013Q4,7.3 514795,14436,2013Q4,4.57 527979,14436,2013Q4,4.64 612550,14436,2013Q4,4.59 147041,14452,2013Q4,14.58 055673,14447,2013Q4,33.05 150044,14447,2013Q4,32.37 154837,14447,2013Q4,35.25 031792,14479,2013Q4,0.08 073010,14479,2013Q4,0.07 086718,14479,2013Q4,0.11 616787,14479,2013Q4,0.14 009725,14451,2013Q4,4.39 117740,14451,2013Q4,4.1 078132,14459,2013Q4,0 137577,14459,2013Q4,0 163308,14459,2013Q4,0.01 165788,14459,2013Q4,0 609644,14459,2013Q4,0.01 000478,10560,2004Q3,0.05 005469,10560,2004Q3,0.0003 043594,10560,2004Q3,0.001 000478,10560,2004Q4,0.02 005469,10560,2004Q4,0.0294 043594,10560,2004Q4,0.0039 044775,10560,2004Q4,0.0446 071785,10560,2004Q4,0.0181 087125,10560,2004Q4,0.04 629762,10560,2004Q4,0.06 000478,10560,2005Q1,0.02 005469,10560,2005Q1,0.0534 043594,10560,2005Q1,0.0526 044775,10560,2005Q1,0.0215 087125,10560,2005Q1,0.02 629762,10560,2005Q1,0.01 000478,10560,2005Q2,0.04 005469,10560,2005Q2,0.0102 043594,10560,2005Q2,0.0015 044775,10560,2005Q2,0.0295 087125,10560,2005Q2,0 629762,10560,2005Q2,0.04 000478,10560,2005Q3,0.07 005469,10560,2005Q3,0.0782 043594,10560,2005Q3,0.0313 044775,10560,2005Q3,0.0409 087125,10560,2005Q3,0.02 629762,10560,2005Q3,0.07 000478,10560,2005Q4,0.02 005469,10560,2005Q4,0.0316 043594,10560,2005Q4,0.0312 044775,10560,2005Q4,0.0142 071785,10560,2005Q4,0.0217 087125,10560,2005Q4,0.03 629762,10560,2005Q4,0.04 000478,10560,2006Q1,0.01 005469,10560,2006Q1,0.0015 043594,10560,2006Q1,0.0015 044775,10560,2006Q1,0.016 071785,10560,2006Q1,0.028 087125,10560,2006Q1,0.01 629762,10560,2006Q1,0.0015 000478,10560,2006Q2,0.05 005469,10560,2006Q2,0.0067 043594,10560,2006Q2,0.0069 044775,10560,2006Q2,0.0246 087125,10560,2006Q2,0.0246 629762,10560,2006Q2,0.016 005469,10560,2006Q3,0.111 043594,10560,2006Q3,0.1111 044775,10560,2006Q3,0.0664 087125,10560,2006Q3,0.06 629762,10560,2006Q3,0.09 000478,10560,2006Q4,0.55 005469,10560,2006Q4,0.0404 043594,10560,2006Q4,0.1466 044775,10560,2006Q4,0.0119 087125,10560,2006Q4,0.01 000478,10560,2007Q1,0.12 005469,10560,2007Q1,0.1308 043594,10560,2007Q1,0.0572 044775,10560,2007Q1,0.1048 087125,10560,2007Q1,0.04 629762,10560,2007Q1,0.0604 000478,10560,2007Q2,0.05 043594,10560,2007Q2,0.033 044775,10560,2007Q2,0.0023 087125,10560,2007Q2,0.06 629762,10560,2007Q2,0.05 000090,24643,1983Q4,0.15 000513,24643,1983Q4,0.19 006754,24643,1983Q4,0.44 009113,24643,1983Q4,0.28 030981,24643,1983Q4,0.15 033096,24643,1983Q4,0.39 510753,24643,1983Q4,0.39 643737,24643,1983Q4,0.19 647292,24643,1983Q4,0.39 000090,24643,1984Q2,0.32 001273,24643,1984Q2,0.42 009326,24643,1984Q2,0.07 030201,24643,1984Q2,0.22 030981,24643,1984Q2,0.17 577089,24643,1984Q2,0.42 634622,24643,1984Q2,0.22 000090,24643,1984Q3,0.63 000458,24643,1984Q3,0.5 001273,24643,1984Q3,0.33 009326,24643,1984Q3,0.48 030981,24643,1984Q3,0.63 000090,24643,1984Q4,0.66 000458,24643,1984Q4,0.8 000513,24643,1984Q4,0.7 006754,24643,1984Q4,0.72 009326,24643,1984Q4,0.81 018278,24643,1984Q4,1.13 030201,24643,1984Q4,0.98 030981,24643,1984Q4,1.05 577089,24643,1984Q4,0.87 634622,24643,1984Q4,0.82 000090,24643,1985Q1,0.22 000458,24643,1985Q1,0.42 000471,24643,1985Q1,0.32 000474,24643,1985Q1,0.17 006754,24643,1985Q1,0.42 009326,24643,1985Q1,0.77 018278,24643,1985Q1,0.57 030981,24643,1985Q1,0.17 577089,24643,1985Q1,0.22 634622,24643,1985Q1,0.17 643737,24643,1985Q1,0.37 000090,24643,1985Q2,0.34 000458,24643,1985Q2,0.24 000513,24643,1985Q2,0.39 006754,24643,1985Q2,0.44 009326,24643,1985Q2,0.39 018278,24643,1985Q2,0.49 030981,24643,1985Q2,0.29 033096,24643,1985Q2,0.41 577089,24643,1985Q2,0.34 643737,24643,1985Q2,0.39 000090,24643,1985Q3,0.47 000458,24643,1985Q3,0.43 000513,24643,1985Q3,0.38 000605,24643,1985Q3,0.08 006754,24643,1985Q3,0.23 009326,24643,1985Q3,0.09 018278,24643,1985Q3,0.48 030981,24643,1985Q3,0.28 033096,24643,1985Q3,0.53 577089,24643,1985Q3,0.28 000090,24643,1985Q4,2.01 000458,24643,1985Q4,1.42 000462,24643,1985Q4,1.77 000479,24643,1985Q4,0.02 000605,24643,1985Q4,2.16 006754,24643,1985Q4,0.22 009326,24643,1985Q4,1.92 030981,24643,1985Q4,0.02 033096,24643,1985Q4,2.12 577089,24643,1985Q4,1.67 634622,24643,1985Q4,1.92 643737,24643,1985Q4,1.86 000090,24643,1986Q1,0.29 000462,24643,1986Q1,0.49 000479,24643,1986Q1,0.01 000605,24643,1986Q1,0.89 006754,24643,1986Q1,0.09 009326,24643,1986Q1,0.39 030981,24643,1986Q1,0.39 033096,24643,1986Q1,0.64 577089,24643,1986Q1,0.23 634622,24643,1986Q1,0.44 643737,24643,1986Q1,0.19 000090,24643,1986Q2,0.02 000462,24643,1986Q2,0.08 000479,24643,1986Q2,0.03 000481,24643,1986Q2,0.13 000605,24643,1986Q2,0.03 006754,24643,1986Q2,0.28 009326,24643,1986Q2,0.23 030981,24643,1986Q2,0.13 577089,24643,1986Q2,0.38 634622,24643,1986Q2,0.18 643737,24643,1986Q2,0.02 000090,24643,1986Q3,0.06 000298,24643,1986Q3,0.31 000462,24643,1986Q3,0.14 000479,24643,1986Q3,0.03 000481,24643,1986Q3,0.12 009326,24643,1986Q3,0.09 030981,24643,1986Q3,0.24 577089,24643,1986Q3,0.29 634622,24643,1986Q3,0.04 000090,24643,1986Q4,0.41 000297,24643,1986Q4,0.23 000298,24643,1986Q4,0.3 000462,24643,1986Q4,0.96 000479,24643,1986Q4,0.32 000481,24643,1986Q4,0.24 009326,24643,1986Q4,0.45 030981,24643,1986Q4,0.1 033096,24643,1986Q4,0.55 538130,24643,1986Q4,0.15 577089,24643,1986Q4,0.35 634622,24643,1986Q4,0.2 000090,24643,1987Q2,1.69 000297,24643,1987Q2,1.63 000298,24643,1987Q2,1.68 000462,24643,1987Q2,1.53 000471,24643,1987Q2,1.63 000479,24643,1987Q2,1.53 000481,24643,1987Q2,1.57 000485,24643,1987Q2,1.63 006754,24643,1987Q2,1.43 030981,24643,1987Q2,1.48 577089,24643,1987Q2,1.63 634622,24643,1987Q2,1.78 643737,24643,1987Q2,1.68 000090,24643,1987Q3,0.06 000297,24643,1987Q3,0.54 000298,24643,1987Q3,0.24 000462,24643,1987Q3,0.19 000471,24643,1987Q3,0.34 000479,24643,1987Q3,0.24 000481,24643,1987Q3,0.15 000485,24643,1987Q3,0.09 040316,24643,1987Q3,0.01 634622,24643,1987Q3,0.24 643737,24643,1987Q3,0.19 000090,24643,1987Q4,0.23 000297,24643,1987Q4,0.5 000298,24643,1987Q4,0.1 000462,24643,1987Q4,0.25 000471,24643,1987Q4,0.28 000479,24643,1987Q4,0.2 000481,24643,1987Q4,0.3 000485,24643,1987Q4,0.35 005303,24643,1987Q4,0.25 634622,24643,1987Q4,0.16 000090,24643,1988Q1,0.67 000297,24643,1988Q1,1.02 000462,24643,1988Q1,0.52 000471,24643,1988Q1,0.52 000481,24643,1988Q1,0.67 033096,24643,1988Q1,0.92 643737,24643,1988Q1,0.67 000090,24643,1988Q2,0.69 000297,24643,1988Q2,0.59 000298,24643,1988Q2,0.39 000462,24643,1988Q2,0.44 000471,24643,1988Q2,0.49 000479,24643,1988Q2,0.64 000481,24643,1988Q2,0.49 033096,24643,1988Q2,1.44 568633,24643,1988Q2,0.43 634622,24643,1988Q2,0.19 643737,24643,1988Q2,0.44 000090,24643,1988Q3,0.02 000297,24643,1988Q3,0.46 000298,24643,1988Q3,0.26 000462,24643,1988Q3,1.06 000471,24643,1988Q3,0.19 000479,24643,1988Q3,0.09 000481,24643,1988Q3,0.34 000485,24643,1988Q3,0.43 033096,24643,1988Q3,0.36 568633,24643,1988Q3,0.22 634622,24643,1988Q3,0.06 643737,24643,1988Q3,0.34 000090,24643,1988Q4,0.23 000297,24643,1988Q4,0.19 000298,24643,1988Q4,0.17 000471,24643,1988Q4,0.03 000479,24643,1988Q4,0.24 000481,24643,1988Q4,0.13 000485,24643,1988Q4,0.18 004674,24643,1988Q4,0.03 033096,24643,1988Q4,0.13 568633,24643,1988Q4,0.23 577089,24643,1988Q4,0.07 634622,24643,1988Q4,0.37 643737,24643,1988Q4,0.07 000090,24643,1989Q1,0.17 000471,24643,1989Q1,0.52 000474,24643,1989Q1,0.3 000479,24643,1989Q1,0.49 000481,24643,1989Q1,0.42 000485,24643,1989Q1,0.67 004674,24643,1989Q1,0.72 033096,24643,1989Q1,0.32 568633,24643,1989Q1,0.07 577089,24643,1989Q1,0.37 ;;;; ... View more

Dear SAS commnunity, I am trying to "clean up" company names by removing strings like CORP, INC, LP and so on from the end of the company name. I am also removing single letters at the end of the company name. The problem with the code is that it removes these strings if they are inside the company name. For example "TEXAS GENCO LP" after the code is applied "TEXAS GENCO" but "LP MINERAL LLC" is reduced incorrectly to "MINERAL". What is wrong with my code?     data test; input cname $60.; CNAME1 = compbl(translate(CNAME, " ", ";.,:()-/")); CNAME1=prxchange('s/\s\S{1,2}\s*$//o', 1, CNAME1); CNAME1 = prxchange("s/\b(INC|PA|PLC|II|III|U S|BV|MI|MICA|1 LLC|B V|GR|SP|LLC|US|USA|LP|CORP|LTD|LT|CO|NV|LP|L P|LL|IN|IND|COR|SA|KG|F K|F K A|AB)\b//o", -1, CNAME1); CNAME1=prxchange('s/\&$//',-1,strip(CNAME1)); cards; TEXAS GENCO LP LP MINERAL LLC A G SIMPSON AUTOMOTIVE & A G SIMPSON AUTOMOTIVE A A G SIMPSON AUTOMOTIVE I A G SIMPSON AUTOMOTIVE C A G SIMPSON AUTOMOTIVE U A G SIMPSON AUTOMOTIVE H A G SIMPSON AUTOMOTIVE H A G SIMPSON AUTOMOTIVE PA A G SIMPSON AUTOMOTIVE INC ;   ... View more

 Hi guys: I am looking for a smart solution to remove the last character (last two characters) from a string that is preceded by a space(blank). I know how to do it for the & character but I am not sure about the others. I have more than 23,000 company names and I would not like to strip these company names from letters accidentally.    data test; input cname $40.; string=prxchange('s/\&&$//',-1,strip(ur_string)); cards; A G SIMPSON AUTOMOTIVE & A G SIMPSON AUTOMOTIVE A A G SIMPSON AUTOMOTIVE I A G SIMPSON AUTOMOTIVE C A G SIMPSON AUTOMOTIVE U A G SIMPSON AUTOMOTIVE H A G SIMPSON AUTOMOTIVE H A G SIMPSON AUTOMOTIVE PA ; run;   ... View more

I have two datasets have1 and have2 and I would like to merge them by company name. I walso would like an additional restriction that the year of the first table has to be withing the start and end date of  have2. The leading dataset is have1. I tried the spedis function but I get a warning about a Cartesian product and the resulting dataset is humongous. I can't stop the code. What is the best way to match? I know there is compged, soundex and so on but what is best in this case? Is there a way to match using prxchange (the Perl syntax). Could you please help.     proc sql; create want as select a.*,b.* from have1 as a, have2 as b group by a.CNAME (a.year>=b.year(start)) and (a.year<=b.year(end)) having spedis(a.CNAME,b.COMNAM)=min(spedis(a.CNAME,b.COMNAM)); quit;     data WORK.HAVE1; infile datalines dsd truncover; input year:BEST12. CNAME:$20.; format year BEST12.; datalines4; 1992,AGILENT TECHNOLOGIES 1993,AGILENT TECHNOLOGIES 1994,AGILENT TECHNOLOGIES 1995,AGILENT TECHNOLOGIES 1996,AGILENT TECHNOLOGIES 1997,AGILENT TECHNOLOGIES 1998,AGILENT TECHNOLOGIES 1999,AGILENT TECHNOLOGIES 2001,AGILENT TECHNOLOGIES 2003,AGILENT TECHNOLOGIES 2004,AGILENT TECHNOLOGIES 2015,AGILENT TECHNOLOGIES 1999,AGILENT TECHS 2001,AGILENT TECHS 1998,ALCOA 2003,ALCOA 2004,ALCOA 2005,ALCOA 2006,ALCOA 2007,ALCOA 2009,ALCOA 2010,ALCOA 2011,ALCOA 2012,ALCOA 2014,ALCOA 2015,ALCOA 1996,AAON 1987,APPLE COMPUTER 1988,APPLE COMPUTER 1989,APPLE COMPUTER 1990,APPLE COMPUTER 1991,APPLE COMPUTER 1992,APPLE COMPUTER 1996,THOMAS & BETTS 1997,THOMAS & BETTS 1998,THOMAS & BETTS 1999,THOMAS & BETTS 2001,THOMAS & BETTS 2003,THOMAS & BETTS 1987,THOMAS & BETTS 1988,THOMAS & BETTS 1989,THOMAS & BETTS 1990,THOMAS & BETTS 1991,THOMAS & BETTS 1992,THOMAS & BETTS 1993,THOMAS & BETTS 1994,THOMAS & BETTS 1995,THOMAS & BETTS 1996,THOMAS & BETTS 1997,THOMAS & BETTS 1998,THOMAS & BETTS 1999,THOMAS & BETTS 2001,THOMAS & BETTS 2003,THOMAS & BETTS 2004,THOMAS & BETTS 2005,THOMAS & BETTS 2006,THOMAS & BETTS 2007,THOMAS & BETTS 2009,THOMAS & BETTS 2010,THOMAS & BETTS 2011,THOMAS & BETTS 2012,THOMAS & BETTS 2014,THOMAS & BETTS 2015,THOMAS & BETTS 1996,TYCO ELECTRONICS 1997,TYCO ELECTRONICS 1998,TYCO ELECTRONICS 1999,TYCO ELECTRONICS 2014,ABBVIE 2015,ABBVIE 1987,AMERICAN BUILDINGS 1988,AMERICAN BUILDINGS 1989,AMERICAN BUILDINGS 1990,AMERICAN BUILDINGS 1987,ABENGOA BIOENERGY 1988,ABENGOA BIOENERGY 1989,ABENGOA BIOENERGY 1990,ABENGOA BIOENERGY 1991,ABENGOA BIOENERGY 1992,ABENGOA BIOENERGY 1993,ABENGOA BIOENERGY 1994,ABENGOA BIOENERGY 1995,ABENGOA BIOENERGY 1996,ABENGOA BIOENERGY 1997,ABENGOA BIOENERGY 1998,ABENGOA BIOENERGY 1999,ABENGOA BIOENERGY 2001,ABENGOA BIOENERGY 2003,ABENGOA BIOENERGY 2004,ABENGOA BIOENERGY 2005,ABENGOA BIOENERGY 2006,ABENGOA BIOENERGY 2007,ABENGOA BIOENERGY 2009,ABENGOA BIOENERGY 2010,ABENGOA BIOENERGY 2011,ABENGOA BIOENERGY 2014,ABENGOA BIOENERGY 1992,APPLERA 1993,APPLERA 1994,APPLERA 1995,APPLERA 1996,APPLERA 1997,APPLERA 1999,APPLERA 2001,APPLERA 2003,APPLERA 1987,AMERICAN BILTRITE 1988,AMERICAN BILTRITE 1989,AMERICAN BILTRITE 1990,AMERICAN BILTRITE 1991,AMERICAN BILTRITE 1992,AMERICAN BILTRITE 1993,AMERICAN BILTRITE 1994,AMERICAN BILTRITE 1995,AMERICAN BILTRITE 1996,AMERICAN BILTRITE 1997,AMERICAN BILTRITE 1998,AMERICAN BILTRITE 1999,AMERICAN BILTRITE 2001,AMERICAN BILTRITE 2003,AMERICAN BILTRITE 2004,AMERICAN BILTRITE 2005,AMERICAN BILTRITE 2006,AMERICAN BILTRITE 2007,AMERICAN BILTRITE 2009,AMERICAN BILTRITE 2010,AMERICAN BILTRITE 2011,AMERICAN BILTRITE 2012,AMERICAN BILTRITE 2014,AMERICAN BILTRITE 2015,AMERICAN BILTRITE 1989,ABBOTT LABORATORIES 1990,ABBOTT LABORATORIES 1991,ABBOTT LABORATORIES 1992,ABBOTT LABORATORIES 1993,ABBOTT LABORATORIES 1994,ABBOTT LABORATORIES 1995,ABBOTT LABORATORIES 1996,ABBOTT LABORATORIES 1997,ABBOTT LABORATORIES 1998,ABBOTT LABORATORIES 1999,ABBOTT LABORATORIES 2004,ABBOTT LABORATORIES 2005,ABBOTT LABORATORIES 2006,ABBOTT LABORATORIES 2007,ABBOTT LABORATORIES 2009,ABBOTT LABORATORIES 2010,ABBOTT LABORATORIES 2011,ABBOTT LABORATORIES 2012,ABBOTT LABORATORIES 2014,ABBOTT LABORATORIES 2015,ABBOTT LABORATORIES 1987,ABBOTT LABS 1988,ABBOTT LABS 1989,ABBOTT LABS 1990,ABBOTT LABS 1991,ABBOTT LABS 1992,ABBOTT LABS 1993,ABBOTT LABS 1994,ABBOTT LABS 1995,ABBOTT LABS 1996,ABBOTT LABS 1997,ABBOTT LABS 1998,ABBOTT LABS 1999,ABBOTT LABS 2001,ABBOTT LABS 1987,ACETO 1988,ACETO 1989,ACETO 1990,ACETO 1987,AMCOL INTERNATIONAL 1990,AMCOL INTERNATIONAL 1991,AMCOL INTERNATIONAL 1992,AMCOL INTERNATIONAL 1993,AMCOL INTERNATIONAL 1994,AMCOL INTERNATIONAL 1995,AMCOL INTERNATIONAL 1996,AMCOL INTERNATIONAL 1997,AMCOL INTERNATIONAL 1998,AMCOL INTERNATIONAL 1999,AMCOL INTERNATIONAL 2001,AMCOL INTERNATIONAL 2003,AMCOL INTERNATIONAL 2004,AMCOL INTERNATIONAL 2005,AMCOL INTERNATIONAL 2006,AMCOL INTERNATIONAL 2007,AMCOL INTERNATIONAL 2009,AMCOL INTERNATIONAL 2010,AMCOL INTERNATIONAL 2011,AMCOL INTERNATIONAL 2012,AMCOL INTERNATIONAL 1987,ACME UNITED 1988,ACME UNITED 1989,ACME UNITED 1990,ACME UNITED 1991,ACME UNITED 1992,ACME UNITED 1993,ACME UNITED 1994,ACME UNITED 1991,HALSEY DRUG 1992,HALSEY DRUG 1993,HALSEY DRUG 1994,HALSEY DRUG 1995,HALSEY DRUG 1996,HALSEY DRUG 1997,HALSEY DRUG 1998,HALSEY DRUG 1999,HALSEY DRUG 2001,HALSEY DRUG 2003,HALSEY DRUG ;;;; data WORK.HAVE2; infile datalines dsd truncover; input company_id:BEST12. ticker:$4. COMNAM:$25. start:$9. end:$9.; format company_id BEST12.; datalines4; 87432,A,AGILENT TECHNOLOGIES,18-Nov-99,28-Sep-18 16347,AA,ALCOA,1-Nov-16,28-Sep-18 14593,AAPL,APPLE COMPUTER,12-Dec-80,28-Sep-18 38578,TNB,THOMAS & BETTS,27-Sep-62,16-May-12 13721,ABBV,ABBVIE,2-Jan-13,28-Sep-18 80431,ABCO,AMERICAN BUILDINGS CO NEW,29-Apr-94,12-May-99 14236,ABGB,ABENGOA S A,17-Oct-13,27-Apr-16 27713,ABI,APPLERA,30-Nov-00,21-Nov-08 39482,ABL,AMERICAN BILTRITE RUBR,1-Aug-63,26-Jan-12 20482,ABT,ABBOTT LABORATORIES,2-Jul-62,28-Sep-18 10656,ACET,ACETO CHEMICAL,14-Dec-72,28-Sep-18 11599,ACO,AMCOL INTERNATIONAL,22-Sep-98,9-May-14 60038,ACU,ACME UNITED,3-Oct-77,28-Sep-18 10116,HADR,HALSEY DRUG,14-Mar-86,29-Nov-88 ;;;;   ... View more

Dear SAS Community: I have certain data. Basically it is panel data with company_id, year and quarter (qtr). I would like to remove duplicate records.  I know I can use the following command: proc sort data=test nodupkey out=test 1; by year qtr; run; However this randomly removes duplicates. I have certain variables (var1, var2, var3, and var4 that are percentages and I would like to keep the maximum values for each year-quarter for var3 and var4. So for example in 1999 I have two different rows- one for which var3 and var4 are equal to 0.5 and another one for which var3 and var4 are equal to 1. How can I keep the one with the higher value?      data work.TEST; infile datalines dsd truncover; input company_id:BEST12. year:32. qtr:32. var1:32. var2:32. var3:32. var4:32.; format company_id BEST12.; datalines4; 11334,1992,2,0,0,0,0 11334,1992,3,0,0,0,0 11334,1992,3,0,0,0,0 11334,1992,3,0,0,0,0 11334,1992,4,0,0,0,0 11334,1994,2,0,0,0,0 11334,1994,3,0,0,0,0 11334,1994,3,0,0,0,0 11334,1994,3,0,0,0,0 11334,1994,4,0,0,0,0 11334,1995,2,0,0,0,0 11334,1995,3,0,0,0,0 11334,1995,3,0,0,0,0 11334,1995,3,0,0,0,0 11334,1995,4,0,0,0,0 11334,1996,2,0,0,0,0 11334,1996,3,0,0,0,0 11334,1996,3,0,0,0,0 11334,1996,3,0,0,0,0 11334,1996,4,0,0,0,0 11334,1997,2,0,0,0,0 11334,1997,3,0,0,0,0 11334,1997,3,0,0,0,0 11334,1997,3,0,0,0,0 11334,1997,4,0,0,0,0 11334,1998,2,0,0,0,0 11334,1998,3,0,0,0,0 11334,1998,3,0,0,0,0 11334,1998,3,0,0,0,0 11334,1998,4,0,0,0,0 11334,1999,2,0.5,0.5,0.5,0.5 11334,1999,2,1,1,1,1 11334,1999,3,0.5,0.5,0.5,0.5 11334,1999,3,1,1,1,1 11334,1999,3,0.5,0.5,0.5,0.5 11334,1999,3,1,1,1,1 11334,1999,3,0.5,0.5,0.5,0.5 11334,1999,3,1,1,1,1 11334,1999,4,0.5,0.5,0.5,0.5 11334,1999,4,1,1,1,1 11334,2000,2,0,0,0,0 11334,2000,2,0,0,0,0 11334,2000,3,0,0,0,0 11334,2000,3,0,0,0,0 11334,2000,3,0,0,0,0 11334,2000,3,0,0,0,0 11334,2000,3,0,0,0,0 11334,2000,3,0,0,0,0 11334,2000,4,0,0,0,0 11334,2000,4,0,0,0,0 11334,2001,2,0,0,0,0 11334,2001,2,0,0,0,0 11334,2001,3,0,0,0,0 11334,2001,3,0,0,0,0 11334,2001,3,0,0,0,0 11334,2001,3,0,0,0,0 11334,2001,3,0,0,0,0 11334,2001,3,0,0,0,0 11334,2001,4,0,0,0,0 11334,2001,4,0,0,0,0 11334,2002,2,0,0,0,0 11334,2002,2,0,0,0,0 11334,2002,3,0,0,0,0 11334,2002,3,0,0,0,0 11334,2002,3,0,0,0,0 11334,2002,3,0,0,0,0 11334,2002,3,0,0,0,0 11334,2002,3,0,0,0,0 11334,2002,4,0,0,0,0 11334,2002,4,0,0,0,0 11334,2003,2,1,1,1,1 11334,2003,3,1,1,1,1 11334,2003,3,1,1,1,1 11334,2003,3,1,1,1,1 11334,2003,4,1,1,1,1 11334,2004,2,0,0,0,0 11334,2004,3,0,0,0,0 11334,2004,3,0,0,0,0 11334,2004,3,0,0,0,0 11334,2004,4,0,0,0,0 11334,2005,2,0,0,0,0 11334,2005,3,0,0,0,0 11334,2005,3,0,0,0,0 11334,2005,3,0,0,0,0 11334,2005,4,0,0,0,0 11334,2006,2,0,0,0,0 11334,2006,3,0,0,0,0 11334,2006,3,0,0,0,0 11334,2006,3,0,0,0,0 11334,2006,4,0,0,0,0 11334,2007,2,0,0,0,0 11334,2007,3,0,0,0,0 11334,2007,3,0,0,0,0 11334,2007,3,0,0,0,0 11334,2007,4,0,0,0,0 11334,2008,2,0,0,0,0 11334,2008,3,0,0,0,0 11334,2008,3,0,0,0,0 11334,2008,3,0,0,0,0 11334,2008,4,0,0,0,0 11334,2009,2,0,0,0,0 11334,2009,3,0,0,0,0 11334,2009,3,0,0,0,0 11334,2009,3,0,0,0,0 11334,2009,4,0,0,0,0 11334,2011,2,0,0.5,0.5,0.5 11334,2011,3,0,0.5,0.5,0.5 11334,2011,3,0,0.5,0.5,0.5 11334,2011,3,0,0.5,0.5,0.5 11334,2011,4,0,0.5,0.5,0.5 11334,2013,2,0.5,0.5,0.5,0.5 11334,2013,3,0.5,0.5,0.5,0.5 11334,2013,3,0.5,0.5,0.5,0.5 11334,2013,3,0.5,0.5,0.5,0.5 11334,2013,4,0.5,0.5,0.5,0.5 11334,2014,2,0,0,0,0 11334,2014,3,0,0,0,0 11334,2014,3,0,0,0,0 11334,2014,3,0,0,0,0 11334,2014,4,0,0,0,0 11334,2016,2,0,0,0,0 11334,2016,3,0,0,0,0 11334,2016,3,0,0,0,0 11334,2016,3,0,0,0,0 11334,2016,4,0,0,0,0 ;;;; ... View more

Dear SAS Community: I have company names but the problem is that some have INC, CORP, US at the end of the company name. How can I remove "INC", "INC.", "CORP",  "INC A D", "CO" at the end of the company name? I tried substr, prxchange and others but I could not find a good solution because it was removing INC when it was part of the company name.   data WORK.have; infile datalines dsd truncover; input COMPANY_NAME:$100.; datalines4; 3M CO 3M CO 3M CO INC A H BELO CORP A O SMITH CORP A O SMITH ELECTRICAL PRODUCTS A SCHULMAN INC AAR CORP ABBOTT LABORATORIES ABBOTT LABORATORIES ABBOTT LABS ABENGOA BIOENERGY ACCURIDE CORP ACETO CORP ACME UNITED CORP ACTUANT CORP ACUTIY LIGHTING GROUP INC A D ADM ADOLPH COORS CO ADVANSIX INC AEROFLEX INC AEROJET ROCKETDYNE HOLDINGS AGCO CORP AGCO INC AGERE SYSTEMS AGRIUM INC AGRIUM US INC AIR PRODS & CHEMICALS INC AIR PRODUCTS & CHEMICALS INC AK STEEL HOLDING CORP ALBEMARLE CORP ALCOA CORP ALLEGHENY TECHNOLOGIES INC ALLERGAN INC ALLIED MOTION TECHNOLOGY ALLIED-SIGNAL INC ALON USA ENERGY INC ALTAIR ALTRIA GROUP INC ALUMAX INC AMCOL INTERNATIONAL CORP AMERADA HESS CORP AMERCO AMERICAN AXLE & MANUFACTURING AMERICAN BILTRITE INC AMERICAN BUILDINGS CO AMERICAN GREETINGS CORP AMERICAN OUTDOOR BRANDS CORP AMERICAN PACIFIC CORP AMERICAN VANGUARD CORP AMERICAN WOODMARK CORP AMETEK INC "AMETEK, INC" AMPCO-PITTSBURGH CORP AMPHENOL CORP AMPHENOL INC AMREP INC AMTROL INC ANACOMP INC ANALOG DEVICES INC ANCHOR GLASS CONTAINER CORP ANDEAVOR APOGEE ENT APOGEE ENTERPRISES INC APPLE COMPUTER INC APPLIED MATERIALS INC APPLIED POWER INC APTARGROUP INC ARCHER DANIELS MIDLAND ARCHER DANIELS MIDLAND CO ARCHER DANIELS MIDLAND CO ARMSTRONG FLOORING INC ARMSTRONG FLOORING INC ARMSTRONG FLOORING INC. ARMSTRONG WORLD INDL INC ARMSTRONG WORLD INDUSTRIES ARMSTRONG WORLD INDUSTRIES IN ARROW INTERNATIONAL INC ARVINMERITOR INC ASH GROVE CEMENT CO ASSOCIATED MATERIALS INC ASTEC INDUSTRIES INC AT&T CORP AUTOMOTIVE COMPONENTS HOLDING AUTOMOTIVE SAFETY COMPONENTS AVAYA INC AVERY DENNISON CORP AXIALL CORP AZZ INC B/E AEROSPACE INC BACARDI CORP BADGER METER INC BALCHEM CORP BALL CORP BANDAG INC BARNES GROUP INC BASSETT FURNITURE IND INC BASSETT FURNITURE INDS INC BASSETT FURNITURE INDUSTRIES BAXTER HEALTHCARE CORP BAXTER INTERNATIONAL INC BE AEROSPACE BE AEROSPACE INC BECTON DICKINSON & CO BEL FUSE INC BELDEN CDT BELDEN CDT INC BELDEN INC BELL INDUSTRIES INC BEMIS CO INC BENCHMARK ELECTRONICS INC BERKSHIRE HATHAWAY BERKSHIRE HATHAWAY BERKSHIRE HATHAWAY INC BERKSHIRE HATHAWAY INC BERKSHIRE HATHAWAY INC BIC CORP BIC USA INC BIO-RAD LABORATORIES INC BLACK & DECKER CORP BLYTH INDL INC BLYTH INDL INC BLYTH INDUSTRIES INC BLYTH INDUSTRIES INC BOEING CO BOISE CASCADE CO BOISE CASCADE CORP BOMBARDIER MOTOR CORP OF AMER BORGWARNER AUTOMOTIVE INC BORGWARNER INC BOSTON SCIENTIFIC CORP BRADY CORP BREED TECHS BREED TECHS INC BRIGGS & STRATTON CORP BRISTOL-MYERS SQUIBB CO BROWN FORMAN CORP BROWN GROUP INC BROWN-FORMAN CORP BRUNSWICK CORP BRUSH ENGINEERED MATERIALS BUCKEYE PARTNERS L.P. BUCKEYE PORT READING TERMINAL BURKE-PARSONS-BOWLBY CORP BURNHAM HOLDINGS INC BWAY CORP BWAY HOLDING CO BWX TECHNOLOGIES INC BWX TECHNOLOGIES INC. C&D TECHNOLOGIES INC ;;;; ... View more