Dear Users,
I used a macro below for finding the next benchmark when returns do not exist.
I am facing the errors attached in the log file.
%let nindexmin=5 /*I set arbitrary number greater than zero*/;
%macro findnextbenchmark;
%DO %WHILE (&nindex>0 and &nindexmin>0);
/*First time*/
data MinBench;
set FirmsBench6;
by cusip;
/* Prevents LOWEST from being reset*/
/* to missing for each iteration. */
retain minimum;
/* Sets HIGHEST to missing for each */
/* different ID value. */
If first.cusip then minimum=.;
/* Compares lowest to diff of BMs in */
/* current iteration and resets */
/* value if Diff of BM is lower. */
minimum=min(minimum,Diff_BM);
if first.cusip then output;
run;
proc sql;
create table temp as
select *
from missing a
left join MinBench b
on a.cusip=b.cusip;
quit;
proc sort data=temp out=fdf nodupkeys;
by cusip;
run;
data temp(keep=CUSIP Conditional_Exercise_Date cusip_ben date_ipo retx_ipo countdays);
set temp;
run;
proc sort data=temp;
by cusip countdays;
run;
/*Updated*/
proc sql;
create table FirmsBench6 as
select a.*
from FirmsBench6 a
left join MinBench b
on a.cusip=b.cusip
where a.cusip_ben not in (select b.cusip_ben from MinBench);
quit;
proc sort data= FirmsBench6;
by CUSIP ;
run;
%let psw=xxxxxxxx;
%let username=xxxxxxxx;
/* Push the macro variables to the remote server (without the next line the WRDS server
would not 'understand' &vars etc); */
%let wrds = wrds.wharton.upenn.edu 4016;options comamid = TCP remote=WRDS;
signon noscript user=xxxxx' password="&psw";
rsubmit;
libname crsplib '/wrds/crsp/sasdata/a_stock';
proc upload data=temp out=work.temp ; run;
/*Get returns after conditional exercise date*/
PROC SQL;
create table temp_1 as
select a.* , b.retx as retx_ben
from work.temp a
left join crsplib.dsf b
on a.cusip_ben=b.cusip and a.date_ipo=b.date;
quit;
proc download data=temp_1 out=temp_1;
run;
endrsubmit;
signoff;
data temp_2/*(drop=retx_ben1)*/;
set temp_1;
if retx_ben ne .;
if retx_ben ne .B;
if retx_ben ne .C;
*retx_ben1=retx_ben+0;
*if retx_ben1 ne . ;
run;
proc append base=styled_base data=temp_2;
run;
proc sort data=styled_base;
by cusip date_ipo;
run;
data Missing(drop=cusip_ben retx_ben);
set temp_1;
if retx_ben= . or retx_ben=.B or retx_ben=.C;
run;
proc sort data=Missing out=Missigben nodupkeys;
by cusip;
run;
data _null_;
call symput('nindex',put(obs,best.));
stop;
set Missigben nobs=obs;
run;
data _null_;
call symput('nindexmin',put(obs,best.));
stop;
set WORK.FIRMSBENCH6 nobs=obs;
run;
*%if &nindexmin = 0 %then LEAVE;
%put &nindex;
%put &nindexmin;
%END;
%mend;
%findnextbenchmark;
File Error:
91
20688
MPRINT(FINDNEXTBENCHMARK): * data MinBench;
NOTE: Line generated by the invoked macro "FINDNEXTBENCHMARK".
1054 data MinBench; set FirmsBench6;
---
180
MPRINT(FINDNEXTBENCHMARK): set FirmsBench6;
ERROR 180-322: Statement is not valid or it is used out of proper order.
NOTE: Line generated by the invoked macro "FINDNEXTBENCHMARK".
1054 by cusip;
--
180
MPRINT(FINDNEXTBENCHMARK): by cusip;
ERROR 180-322: Statement is not valid or it is used out of proper order.
NOTE: Line generated by the invoked macro "FINDNEXTBENCHMARK".
1054 retain minimum;
------
180
MPRINT(FINDNEXTBENCHMARK): retain minimum;
ERROR 180-322: Statement is not valid or it is used out of proper order.
NOTE: Line generated by the invoked macro "FINDNEXTBENCHMARK".
1054 If first.cusip then minimum=.;
--
many thanks in advance
Note the asterisk in the code generated by your macro, it's commenting out the DATA statement. so the SET statement errors:
MPRINT(FINDNEXTBENCHMARK): * data MinBench; NOTE: Line generated by the invoked macro "FINDNEXTBENCHMARK". 1054 data MinBench; set FirmsBench6; --- 180 MPRINT(FINDNEXTBENCHMARK): set FirmsBench6;
That asterisk is not in your macro definition on the DATA statement. So where is the * coming from? Well, it's at the end of your macro:
*%if &nindexmin = 0 %then LEAVE;
That line is returning a * which you do not intend. The * is not a comment in the macro language. If you want that to be a macro comment you need to use %*, i.e.:
%* if &nindexmin = 0 %then LEAVE;
Or you could use /* */ :
/*
%if &nindexmin = 0 %then LEAVE;
*/
you can preview before downloading it but I agree with you that it's better to post it in the contents of the post itself.
can you run the same code successfully outside the macro? and for debugging, it would be helpful if you turn on the options mprint mlogic symbolgen.
Yes if I do it manually it works. the table FirmsBenchmark 6 is updated every time with fewer observations!
can you post the log using </> after running with MPRINT MLOGIC and SYMBOLGEN options?
Note the asterisk in the code generated by your macro, it's commenting out the DATA statement. so the SET statement errors:
MPRINT(FINDNEXTBENCHMARK): * data MinBench; NOTE: Line generated by the invoked macro "FINDNEXTBENCHMARK". 1054 data MinBench; set FirmsBench6; --- 180 MPRINT(FINDNEXTBENCHMARK): set FirmsBench6;
That asterisk is not in your macro definition on the DATA statement. So where is the * coming from? Well, it's at the end of your macro:
*%if &nindexmin = 0 %then LEAVE;
That line is returning a * which you do not intend. The * is not a comment in the macro language. If you want that to be a macro comment you need to use %*, i.e.:
%* if &nindexmin = 0 %then LEAVE;
Or you could use /* */ :
/*
%if &nindexmin = 0 %then LEAVE;
*/
noticed that too! I like to comment code by highlighting it and pressing CTRL + shift.
Thank you so mush sir!
I have removed the whole sentence and works. Yesterday it was working. This is the only thing I changed today!
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.