Hi @mkeintz, thank you for your response. Please find below for the SAS log when I run your code. The problem might be that my SAS system (windows) is too old and there might be some functions not available. The advantage of your code is that it is very fast. I will come back to @Tom soon. Thank you very much! Best regards, *** NOTE: PROCEDURE PRINTTO used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 141 data have; 142 input gvkey $6. sic fyear item :$5. ; 143 datalines; NOTE: The data set WORK.HAVE has 12 observations and 4 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 156 run; 157 proc sort data=have out=have_srt; 158 by sic fyear gvkey item; 159 run; NOTE: SAS threaded sort was used. NOTE: There were 12 observations read from the data set WORK.HAVE. NOTE: The data set WORK.HAVE_SRT has 12 observations and 4 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.03 seconds cpu time 0.01 seconds 160 data want (keep=gvkeyi gvkeyj sic fyear gvkey_i_total gvkey_j_total 160! ij_intersection) ; 161 array _gvkeyid {200} $6; /* List of gvkey's for current SIC/FYEAR */ 162 array _itemcount {200}; /* Count of items by gvkey, for current 162! SIC/FYEAR */ 163 array _itemmatrix {200,200} $5 ; 164 if 0 then set have_srt /* Establish variables in the PDV, without 164! reading data*/ 165 have_srt (rename=(gvkey=gvkeyi)) 166 have_srt (rename=(gvkey=gvkeyj)); 167 call missing(gvkey_i_total,gvkey_j_total,ij_intersection); 168 if _n_=1 then do; 169 declare hash itemlist(); /*gvkey/item Lookup table, within each 169! SIC/FYEAR*/ 170 itemlist.definekey('gvkey','item'); 171 itemlist.definedone(); 172 declare hash ij_count(); /*For counting up gvkeyi/gvkeyj item 172! intersection*/ 173 ij_count.definekey('gvkeyi','gvkeyj'); 174 ij_count.definedata('ij_intersection'); 175 ij_count.definedone(); 176 end; 177 itemlist.clear(); 178 ij_count.clear(); 179 do g=1 by 1 until (last.fyear); /* Read all records for every gvkey 179! in current SIC/FYEAR */ 180 do i=1 by 1 until (last.gvkey); /* Read each item for current gvkey 180! */ 181 set have_srt ; 182 by sic fyear gvkey; 183 _itemmatrix{g,i}=item ; /* Populate matrix ... */ 184 itemlist.add(); /* ... and the analogous lookup table 184! */ 185 end; 186 _gvkeyid{g}=gvkey; 187 _itemcount{g}=i; 188 end; 189 /* Now process the accumulated data for this SIC/FYEAR */ 190 do i=1 to g; /* For each gvkeyi ... */ 191 gvkeyi=_gvkeyid{i}; 192 gvkey_i_total=_itemcount{i}; 193 do j=1 to g; /* For each gvkeyj ... */ 194 if j=i then continue; /* Skip loop if i=j */ 195 gvkeyj=_gvkeyid{j}; 196 gvkey_j_total=_itemcount{j}; 197 ij_intersection=0; /* Count common items */ 198 if j>i then do k=1 to _itemcount{i}; 199 item=_itemmatrix{i,k}; 200 if itemlist.find(key:gvkeyj,key:item)=0 then 200! ij_intersection=ij_intersection+1; 201 end; 202 if j>i then ij_count.add(); 203 else ij_count.find(key:gvkeyj,key:gvkeyi); 204 output; 205 end; 206 end; 207 run; ERROR: Could not find method. ERROR: Could not find method. ERROR: Could not find method. ERROR: Could not find method. NOTE: The SAS System stopped processing this step because of errors. NOTE: There were 12 observations read from the data set WORK.HAVE_SRT. WARNING: The data set WORK.WANT may be incomplete. When this step was stopped there were 6 observations and 7 variables. WARNING: Data set WORK.WANT was not replaced because this step was stopped. NOTE: DATA statement used (Total process time): real time 0.12 seconds cpu time 0.12 seconds
... View more
