Problem with creating a table in a macro using proc sql

[Ramiro_iese]

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;

[PaigeMiller]

If the log has detected problems, show us the log. Show us the ENTIRE log for one loop of the macro (so change the code to read %do i=1984 %to 1984;), we need to see all of the log, every single line, every single character. Since this is a macro, you also need to turn on debugging options by running this line of code, then re-running the macro, and then showing us the ENTIRE log.

options mprint;

Please paste the log into the window that appears when you click on the </> icon

[Ramiro_iese]

Thank you for your response! here is the log for one iteration. The macro is in line 526.

Thank you again! 

[PaigeMiller]

The line

options mprint;

should go at the top of your program, not at the bottom. Please run the code again after making this change.

Please copy the log as text and paste it into the window that appears when you click on the </> icon. Do not attach files. Thank you.

[Quentin · Accepted solution]

When debugging, SAS, you should review the log an look for the first error (or bad warning/note).  In this case, the error message is immediately after the macro call:

649  %rollsic_new;
ERROR: A character operand was found in the %EVAL function or
       %IF condition where a numeric operand is required. The
       condition was: i-4
ERROR: A character operand was found in the %EVAL function or
       %IF condition where a numeric operand is required. The
       condition was: i

This message is saying that the macro language is trying to do integer arithmetic with a value, but the value is character.  If you look at the start of your macro definition, you have:

%let lbound = i-4;
%let ubound = i;

This will give lbound the value i-4, meaning it a a text value three characters long. Since you intend i to be a macro variable reference, you should change to:

%let lbound = %eval(&i-4) ;
%let ubound = &i ;

Note that I used the %eval function to tell the macro processer to do subtraction.  Without using %eval, your code might work, but the value of lbound would be for example 1984-4, rather than 1980.

Another tip is to turn on the system option MPRINT, so that the log will show you the SAS code generated by your macro.  It's very difficult to debug macros without the MPRINT option turned on.