data have; input ID year value; cards; 1001 1992 1 1001 1993 2 1001 1994 3 1001 1995 4 1001 1996 5 1002 2001 6 1002 2002 7 1002 2003 8 1003 1995 9 1003 1996 10 1003 1997 11 1003 1998 12 1003 1999 13 1003 2000 14 1003 2001 15 1003 2002 16 1003 2003 17 1004 2002 18 1004 2003 19 ; data _null_; if _n_=1 then do; dcl hash H (ordered: "A") ; h.definekey ("year") ; h.definedata ("year","Average","_n_","_iorc_") ; h.definedone () ; end; set have end=z; if n(value); if h.find()=0 then do; _n_=sum(_n_,1); _iorc_=sum(value,_iorc_); end; else do; _iorc_=value; _n_=1; end; Average=_iorc_/_n_; h.replace(); if z; h.output(dataset:'want(drop=_:)'); run; proc print noobs;run; year Average 1992 1.0000 1993 2.0000 1994 3.0000 1995 6.5000 1996 7.5000 1997 11.0000 1998 12.0000 1999 13.0000 2000 14.0000 2001 10.5000 2002 13.6667 2003 14.6667 ... View more

Hi @dapenDaniel    Your question looks very similar to a previous one you posted. Here is an attempt to do that: proc sort data=have; by FirmID Var1 Year; run; data have1; set have; by FirmID Var1 Year; retain Year_ref; if first.Var1 then Year_ref = year; if first.Var1 or Year >= (Year_ref + 3) then output; drop Year_ref; run; proc sort data=have1 out=want; by FirmID Year; run; ... View more

I suggest that you also look at the results for @novinosrin's Proc Summary solution without the NWAY option and removing the (drop=) option on the output set.   Proc summary can create LOTS of different groups of summaries with a single pass through the data which can be very helpful for some tasks as you may not need to create multiple data sets but just select the appropriate _type_ value. ... View more

Thanks, @novinosrin ! You are awesome! I appreciate your help!!! ... View more

Thanks, @novinosrin  ... View more

Hi @mkeintz ,   Thanks for your reply. You are correct. The number for 2007 should be 2. Thanks for correcting me! ... View more

@dapenDaniel wrote: Hi @andreas_lds   Thanks for your reply. I have revised my question. The logic is if one name (like Bloomgerg, Jack B.) is contained in the full name (like Bloomberg, Jack Beta) in the same company, then delete the short name and keep the completed name. Maybe this is a language problem, but Bloomberg, Jack B. is not part of Bloomberg, Jack Beta At least not, if punctuation marks are not removed before comparison. ... View more

I agree with @PaigeMiller that you probably don't have to make the dummy variables for many SAS analysis procedures.   But in case you have to here: data have; input FirmID $ Begin End; cards; 24356W 1994 1999 25537H 1990 1996 run; data want (drop=y); set have; array dum {1990:1999} yd1990-yd1999 ; do y=lbound(dum) to hbound(dum); ** Initialize the array**; dum{y}=0; end; do y=begin to end; ** Write out 1 per year **; dum{y}=1; output; dum{y}=0; end; run; There are 2 features of the ARRAY statement particularly useful here: You can specify the upper and lower bound of the array index,  Here they are 1990 and 1999.  That way, for Y=1990,  DUM{Y}=1 assigns a 1 to the first element in the DUM array. You can name whatever variables you want for the elements of the array.  The names would default to  DUM1, DUM2,. … DUM10, but I replaced those default names with YD1990, YD1991, … YD1999. ... View more

data test; input id name$ ; datalines; 1001 judy 1002 louise 1003 mary 1004 philip 1012 robert 1006 ronald 1043 thomas 1008 william ; run; data test1; set test; where id in ( 1001 1004 1012 1043); run; This output like you want rejoinder me    ... View more

Hi @dapenDaniel   If there is only one distinct ID2 for an ID1 group that you clarified earlier, you are basically filling in the blanks right?   In that case , you could take advantage of the automatic remerge of PROC SQL data have; input ID1 $ ID2 $; infile datalines missover; datalines; 10001A 10001A 10001A AAA111 10001A AAA111 10001A AAA111 10001A AAA111 10001A 10001A 10001A 10001B 10001B 10001B AAA111 10001B AAA111 10001B AAA111 10001B AAA111 10001B 10001B 10001B ; proc sql; create table want(drop=_id2) as select *,max(_ID2) as ID2 from have(rename=id2=_id2) group by id1; quit;   ... View more

For your second question. data temp; if _n_=1 then do; if 0 then set components; declare hash h(dataset:'components',hashexp:20); h.definekey('Year',' Components'); h.definedone(); end; set products; do year=BeginYear-5 to BeginYear-1; /*<------------*/ if h.check()=0 then do;found=1;leave;end; end; drop year; run; proc sql; create table temp2 as select FirmID,BeginYear,ProductID,ifn(sum(found)>0,1,0) as flag from temp group by FirmID,BeginYear,ProductID ; create table want as select FirmID,BeginYear,sum(flag) as nb_Incremental,sum(flag=0) as nb_Radical from temp2 group by FirmID,BeginYear ; quit; ... View more