Hi! I have a problem with a proc sql inside a macro. The log says that the work.segment6.data does not exist while I am creating it. Any ideas? %macro rollsic_new; %do i=1984 %to 2015; %let lbound = i-4; %let ubound = i; proc sql; create table segments6(where=(sics ne . and share ge 0)) as select distinct gvkey, input(sics,12.) as sics, mean(share) as share from segments5 where year(datadate) between %eval(&lbound) and %eval(&ubound) group by gvkey, sics; quit; proc sql; select min(sics) into:minsics from segments6; quit; proc sql; select max(sics) into:maxsics from segments6; quit; * I select all distinct firms; proc sql; create table du as select distinct gvkey from segments6(keep=gvkey); quit; *Eliminamos duplicados. OUTPUT: vector du (gvkeys de segments6 sin duplicados); * Now select all segments present in that sample; proc sql; create table sics as select distinct sics from segments6(keep=sics) order by sics; quit; *Eliminamos los sics duplicados de segments6 y ordenamos por sics; *OUTPUT: vector sics (gvkeys de segments6 sin duplicados); * Now I append all segments to each firm; proc sql; create table both as select a.gvkey, b.sics from du as a, sics as b order by a.gvkey, b.sics; quit; *unimos los vectores de gvkeys y sics, ordenamos por gvkey y sics (asignamos gvkeys a sics); *OUTPUT: base both; * Now paste the segments6 table to have all info; proc sql; create table both1 as select a.*, b.share from both as a left join segments6 as b on a.gvkey = b.gvkey and a.sics = b.sics; quit; *unimos both con el share de segments6 para gvkey y sics iguales; *mantenemos todas las variables de both y solo share de segments6; *OUTPUT: base both1; * I transpose to have in columns the mgrnones; proc transpose data=both1 out=wseg1(drop=NAME) prefix=sics; by gvkey; id sics; var share; run; *corregimos el drop=NAME por drop=NAME; *De lo contrario, se mantenia la columna de NAME con datapoints de "share"; *Creamos matriz traspuesta: columnas contiene los sics y filas gvkeys; *Data points son los share; *; * Now I calculate the cosine similarity; proc distance data=wseg1 out=segCos method=COSINE shape=square replace; var ratio(sics%eval(&minsics)--sics%eval(&maxsics)); id gvkey; run; *Creamos matriz cuadrada que contiene gvkeys en filas y columnas; *Con cosine similarity en los datapoints; * I transpose the resulting matrix to make it accessible; proc sort data=segcos; by gvkey; run; *ordenamos segcos por gvkeys; proc transpose data=segcos out=segg; by gvkey; run; *Panel que contiene panel de gvkeys con su respectivo cosine similarity; * And put it nicely so I have the main variables and the cosine similarty called t; data sic&i(drop=NAME gvkey rename=(COL1=sic)); retain gvkey1 gvkey2; set segg; year = &i; gvkey1=input(gvkey,12.); gvkey2=input(substr(NAME,2),12.); where input(gvkey,12.) ne input(substr(NAME,2),12.) and COL1 not in (. 0); run; %end; %mend; %rollsic_new;
... View more
