DATA Step, Macro, Functions and more

SAS Macro issue

Reply
Contributor
Posts: 58

SAS Macro issue

Hello friends, I am using the following codes; 


*** average return;
options nonotes;

proc model data=sum_diff2;
by &byvar _name_ &rankvar;
parms a;
exogenous col1 ;
instruments / intonly;
col1=a;
fit col1 / gmm kernel=(bart, %eval(&lag+1), 0);*lag=6;

ods output parameterestimates=param0 fitstatistics=fitresult
OutputStatistics=residual;
quit;

data param0;
set param0;
type='Average ret';
run;

*** CAPM alpha;
options nonotes;
proc model data=sum_diff2;
by &byvar _name_ &rankvar;
parms a b1;
instruments mktrf;
exret =a+b1* mktrf;
fit exret / gmm kernel=(bart, %eval(&lag+1), 0);
ods output parameterestimates=param1 fitstatistics=fitresult
OutputStatistics=residual;
quit;

data param1;
set param1;
type='CAPM Alpha';
if parameter='a';
run;

 

When i run them i get following errors;

WARNING: This CREATE TABLE statement recursively references the target table. A consequence of
this is a possible data integrity problem.
WARNING: Output 'OutputStatistics' was not created. Make sure that the output object name,
label, or path is spelled correctly. Also, verify that the appropriate procedure
options are used to produce the requested output object. For example, verify that the
NOPRINT option is not used.
WARNING: Output 'fitstatistics' was not created. Make sure that the output object name, label,
or path is spelled correctly. Also, verify that the appropriate procedure options are
used to produce the requested output object. For example, verify that the NOPRINT
option is not used.
WARNING: Output 'OutputStatistics' was not created. Make sure that the output object name,
label, or path is spelled correctly. Also, verify that the appropriate procedure
options are used to produce the requested output object. For example, verify that the
NOPRINT option is not used.
WARNING: Output 'fitstatistics' was not created. Make sure that the output object name, label,
or path is spelled correctly. Also, verify that the appropriate procedure options are
used to produce the requested output object. For example, verify that the NOPRINT
option is not used.

 

Please have a looks and suggest something. 
Bundle of tthanks

Trusted Advisor
Posts: 1,607

Re: SAS Macro issue

Show us the SASLOG.

Contributor
Posts: 58

Re: SAS Macro issue

[ Edited ]

@PaigeMiller Please have a look. its macro.

 

 

1188
1189 *use single sort macro to generate long-short spread and alphas,ourt reports in row, outc
1189! reports in column;
1190 %macro singlesort_adj(lag=3, data=meanret, byvar=weight, rankvar=ranksort, timevar= mt,
1190! var=ret, outr=sort1, outc=sort2);
1191
1192 * transpose if multiple key variables;
1193 proc sort data=&data;
1194 by &byvar &rankvar &timevar;
1195 run;
1196
1197 proc transpose data=&data out=data;
1198 by &byvar &rankvar &timevar;
1199 var &var;
1200 run;
1201
1202
1203 *Find H-L difference;
1204 proc sort data=&data out=sum;
1205 by &byvar &timevar &rankvar;
1206 run;
1207
1208
1209 data sum_diff;
1210 set sum;
1211 by &byvar &timevar &rankvar;
1212 if first.&timevar or last.&timevar;
1213 if first.&timevar then &rankvar=1;
1214 if last.&timevar then &rankvar=2;
1215 run;
1216
1217 proc transpose data=sum_diff out=sum_diff2;
1218 by &byvar &timevar;
1219 var &var;
1220 id &rankvar;
1221 run;
1222
1223 data sum_diff2;
1224 set sum_diff2;
1225 &rankvar=99;
1226 col1 = _2 - _1;
1227 drop _2 _1;
1228 run;
1229
1230 data sum_diff2;
1231 set data sum_diff2;
1232 run;
1233
1234 proc sort data=sum_diff2;
1235 by &byvar _name_ &rankvar;
1236 run;
1237
1238 *** link 3 factors to get alpha;
1239 proc sql;
1240 create table sum_diff2 as
1241 select a.*, (a.col1-b.rf*100) as exret, b.mktrf*100 as mktrf, b.smb*100 as smb, b.hml*100
1241! as hml
1242 from sum_diff2 as a left join factors_monthly as b
1243 on a.&timevar = b.mt;
1244 quit;
1245
1246 proc sort data=sum_diff2;
1247 by &byvar _name_ &rankvar &timevar;
1248
1249 data sum_diff2;
1250 set sum_diff2;
1251 if &rankvar=99 then exret=col1;
1252 run;
1253
1254 proc sort data=sum_diff2;
1255 by &byvar _name_ &rankvar &timevar;
1256 run;
1257
1258 *** average return;
1259 options nonotes;
1260
1261 proc model data=sum_diff2;
1262 by &byvar _name_ &rankvar;
1263 parms a;
1264 exogenous col1 ;
1265 instruments / intonly;
1266 col1=a;
1267 fit col1 / gmm kernel=(bart, %eval(&lag+1), 0);*lag=6;
1268
1269 ods output parameterestimates=param0 fitstatistics=fitresult
1270 OutputStatistics=residual;
1271 quit;
1272
1273 data param0;
1274 set param0;
1275 type='Average ret';
1276 run;
1277
1278 *** CAPM alpha;
1279 options nonotes;
1280 proc model data=sum_diff2;
1281 by &byvar _name_ &rankvar;
1282 parms a b1;
1283 instruments mktrf;
1284 exret =a+b1* mktrf;
1285 fit exret / gmm kernel=(bart, %eval(&lag+1), 0);
1286 ods output parameterestimates=param1 fitstatistics=fitresult
1287 OutputStatistics=residual;
1288 quit;
1289
1290 data param1;
1291 set param1;
1292 type='CAPM Alpha';
1293 if parameter='a';
1294 run;
1295
1296 %mend singlesort_adj;
1297
1298 %singlesort_adj;
WARNING: This CREATE TABLE statement recursively references the target table. A consequence of
this is a possible data integrity problem.
WARNING: Output 'OutputStatistics' was not created. Make sure that the output object name,
label, or path is spelled correctly. Also, verify that the appropriate procedure
options are used to produce the requested output object. For example, verify that the
NOPRINT option is not used.
WARNING: Output 'fitstatistics' was not created. Make sure that the output object name, label,
or path is spelled correctly. Also, verify that the appropriate procedure options are
used to produce the requested output object. For example, verify that the NOPRINT
option is not used.
WARNING: Output 'OutputStatistics' was not created. Make sure that the output object name,
label, or path is spelled correctly. Also, verify that the appropriate procedure
options are used to produce the requested output object. For example, verify that the
NOPRINT option is not used.
WARNING: Output 'fitstatistics' was not created. Make sure that the output object name, label,
or path is spelled correctly. Also, verify that the appropriate procedure options are
used to produce the requested output object. For example, verify that the NOPRINT
option is not used.

Trusted Advisor
Posts: 1,607

Re: SAS Macro issue

Not what I was looking for. Please turn on the MPRINT option, run the code again, and let us see the SASLOG.

 

In any event, this is not a macro error, this is an error in your call to PROC SQL and your call to PROC MODEL.

 

Contributor
Posts: 58

Re: SAS Macro issue

[ Edited ]

Hy PaigeMiller
I hope this is thing.
1636 *use single sort macro to generate long-short spread and alphas,ourt reports in row, outc
1636! reports in column;
1637 %macro singlesort_adj(lag=3, data=meanret, byvar=weight, rankvar=ranksort, timevar= mt,
1637! var=ret, outr=sort1, outc=sort2);
1638
1639 * transpose if multiple key variables;
1640 proc sort data=&data;
1641 by &byvar &rankvar &timevar;
1642 run;
1643
1644 proc transpose data=&data out=data;
1645 by &byvar &rankvar &timevar;
1646 var &var;
1647 run;
1648
1649
1650 *Find H-L difference;
1651 proc sort data=&data out=sum;
1652 by &byvar &timevar &rankvar;
1653 run;
1654
1655
1656 data sum_diff;
1657 set sum;
1658 by &byvar &timevar &rankvar;
1659 if first.&timevar or last.&timevar;
1660 if first.&timevar then &rankvar=1;
1661 if last.&timevar then &rankvar=2;
1662 run;
1663
1664 proc transpose data=sum_diff out=sum_diff2;
1665 by &byvar &timevar;
1666 var &var;
1667 id &rankvar;
1668 run;
1669
1670 data sum_diff2;
1671 set sum_diff2;
1672 &rankvar=99;
1673 col1 = _2 - _1;
1674 drop _2 _1;
1675 run;
1676
1677 data sum_diff2;
1678 set data sum_diff2;
1679 run;
1680
1681 proc sort data=sum_diff2;
1682 by &byvar _name_ &rankvar;
1683 run;
1684
1685 *** link 3 factors to get alpha;
1686 proc sql;
1687 create table sum_diff22 as
1688 select a.*, (a.col1-b.rf*100) as exret, b.mktrf*100 as mktrf, b.smb*100 as smb, b.hml*100
1688! as hml
1689 from sum_diff2 as a left join factors_monthly as b
1690 on a.&timevar = b.mt;
1691 quit;
1692
1693 proc sort data=sum_diff22;
1694 by &byvar _name_ &rankvar &timevar;
1695
1696 data sum_diff23;
1697 set sum_diff22;
1698 if &rankvar=99 then exret=col1;
1699 run;
1700
1701 proc sort data=sum_diff23;
1702 by &byvar _name_ &rankvar &timevar;
1703 run;
1704
1705 *** average return;
1706 options nonotes;
1707
1708 proc model data=sum_diff23;
1709 by &byvar _name_ &rankvar;
1710 parms a;
1711 exogenous col1 ;
1712 instruments / intonly;
1713 col1=a;
1714 fit col1 / gmm kernel=(bart, %eval(&lag+1), 0);*lag=6;
1715
1716 ods output parameterestimates=param0 fitstatistics=fitresult
1717 OutputStatistics=residual;
1718 quit;
1719
1720 data param0;
1721 set param0;
1722 type='Average ret';
1723 run;
1724
1725 *** CAPM alpha;
1726 options nonotes;
1727 proc model data=sum_diff23;
1728 by &byvar _name_ &rankvar;
1729 parms a b1;
1730 instruments mktrf;
1731 exret =a+b1* mktrf;
1732 fit exret / gmm kernel=(bart, %eval(&lag+1), 0);
1733 ods output parameterestimates=param1 fitstatistics=fitresult
1734 OutputStatistics=residual;
1735 quit;
1736
1737 data param1;
1738 set param1;
1739 type='CAPM Alpha';
1740 if parameter='a';
1741 if parameter='b1';
1742 run;
1743
1744 %mend singlesort_adj;
1745
1746 %singlesort_adj;
MLOGIC(SINGLESORT_ADJ): Beginning execution.
MLOGIC(SINGLESORT_ADJ): Parameter LAG has value 3
MLOGIC(SINGLESORT_ADJ): Parameter DATA has value meanret
MLOGIC(SINGLESORT_ADJ): Parameter BYVAR has value weight
MLOGIC(SINGLESORT_ADJ): Parameter RANKVAR has value ranksort
MLOGIC(SINGLESORT_ADJ): Parameter TIMEVAR has value mt
MLOGIC(SINGLESORT_ADJ): Parameter VAR has value ret
MLOGIC(SINGLESORT_ADJ): Parameter OUTR has value sort1
MLOGIC(SINGLESORT_ADJ): Parameter OUTC has value sort2
MPRINT(SINGLESORT_ADJ): * transpose if multiple key variables;
MPRINT(SINGLESORT_ADJ): proc sort data=meanret;
MPRINT(SINGLESORT_ADJ): by weight ranksort mt;
MPRINT(SINGLESORT_ADJ): run;
MPRINT(SINGLESORT_ADJ): proc transpose data=meanret out=data;
MPRINT(SINGLESORT_ADJ): by weight ranksort mt;
MPRINT(SINGLESORT_ADJ): var ret;
MPRINT(SINGLESORT_ADJ): run;
MPRINT(SINGLESORT_ADJ): *Find H-L difference;
MPRINT(SINGLESORT_ADJ): proc sort data=meanret out=sum;
MPRINT(SINGLESORT_ADJ): by weight mt ranksort;
MPRINT(SINGLESORT_ADJ): run;
MPRINT(SINGLESORT_ADJ): data sum_diff;
MPRINT(SINGLESORT_ADJ): set sum;
MPRINT(SINGLESORT_ADJ): by weight mt ranksort;
MPRINT(SINGLESORT_ADJ): if first.mt or last.mt;
MPRINT(SINGLESORT_ADJ): if first.mt then ranksort=1;
MPRINT(SINGLESORT_ADJ): if last.mt then ranksort=2;
MPRINT(SINGLESORT_ADJ): run;
MPRINT(SINGLESORT_ADJ): proc transpose data=sum_diff out=sum_diff2;
MPRINT(SINGLESORT_ADJ): by weight mt;
MPRINT(SINGLESORT_ADJ): var ret;
MPRINT(SINGLESORT_ADJ): id ranksort;
MPRINT(SINGLESORT_ADJ): run;
MPRINT(SINGLESORT_ADJ): data sum_diff2;
MPRINT(SINGLESORT_ADJ): set sum_diff2;
MPRINT(SINGLESORT_ADJ): ranksort=99;
MPRINT(SINGLESORT_ADJ): col1 = _2 - _1;
MPRINT(SINGLESORT_ADJ): drop _2 _1;
MPRINT(SINGLESORT_ADJ): run;
MPRINT(SINGLESORT_ADJ): data sum_diff2;
MPRINT(SINGLESORT_ADJ): set data sum_diff2;
MPRINT(SINGLESORT_ADJ): run;
MPRINT(SINGLESORT_ADJ): proc sort data=sum_diff2;
MPRINT(SINGLESORT_ADJ): by weight _name_ ranksort;
MPRINT(SINGLESORT_ADJ): run;
MPRINT(SINGLESORT_ADJ): *** link 3 factors to get alpha;
MPRINT(SINGLESORT_ADJ): proc sql;
MPRINT(SINGLESORT_ADJ): create table sum_diff22 as select a.*, (a.col1-b.rf*100) as exret,
b.mktrf*100 as mktrf, b.smb*100 as smb, b.hml*100 as hml from sum_diff2 as a left join
factors_monthly as b on a.mt = b.mt;
MPRINT(SINGLESORT_ADJ): quit;
MPRINT(SINGLESORT_ADJ): proc sort data=sum_diff22;
MPRINT(SINGLESORT_ADJ): by weight _name_ ranksort mt;
MPRINT(SINGLESORT_ADJ): data sum_diff23;
MPRINT(SINGLESORT_ADJ): set sum_diff22;
MPRINT(SINGLESORT_ADJ): if ranksort=99 then exret=col1;
MPRINT(SINGLESORT_ADJ): run;
MPRINT(SINGLESORT_ADJ): proc sort data=sum_diff23;
MPRINT(SINGLESORT_ADJ): by weight _name_ ranksort mt;
MPRINT(SINGLESORT_ADJ): run;
MPRINT(SINGLESORT_ADJ): *** average return;
MPRINT(SINGLESORT_ADJ): options nonotes;
MPRINT(SINGLESORT_ADJ): proc model data=sum_diff23;
MPRINT(SINGLESORT_ADJ): by weight _name_ ranksort;
MPRINT(SINGLESORT_ADJ): parms a;
MPRINT(SINGLESORT_ADJ): exogenous col1 ;
MPRINT(SINGLESORT_ADJ): instruments / intonly;
MPRINT(SINGLESORT_ADJ): col1=a;
MPRINT(SINGLESORT_ADJ): fit col1 / gmm kernel=(bart, 4, 0);
MPRINT(SINGLESORT_ADJ): *lag=6;
MPRINT(SINGLESORT_ADJ): ods output parameterestimates=param0 fitstatistics=fitresult
OutputStatistics=residual;
MPRINT(SINGLESORT_ADJ): quit;
WARNING: Output 'OutputStatistics' was not created. Make sure that the output object name,
label, or path is spelled correctly. Also, verify that the appropriate procedure
options are used to produce the requested output object. For example, verify that the
NOPRINT option is not used.
WARNING: Output 'fitstatistics' was not created. Make sure that the output object name, label,
or path is spelled correctly. Also, verify that the appropriate procedure options are
used to produce the requested output object. For example, verify that the NOPRINT
option is not used.
MPRINT(SINGLESORT_ADJ): data param0;
MPRINT(SINGLESORT_ADJ): set param0;
MPRINT(SINGLESORT_ADJ): type='Average ret';
MPRINT(SINGLESORT_ADJ): run;
MPRINT(SINGLESORT_ADJ): *** CAPM alpha;
MPRINT(SINGLESORT_ADJ): options nonotes;
MPRINT(SINGLESORT_ADJ): proc model data=sum_diff23;
MPRINT(SINGLESORT_ADJ): by weight _name_ ranksort;
MPRINT(SINGLESORT_ADJ): parms a b1;
MPRINT(SINGLESORT_ADJ): instruments mktrf;
MPRINT(SINGLESORT_ADJ): exret =a+b1* mktrf;
MPRINT(SINGLESORT_ADJ): fit exret / gmm kernel=(bart, 4, 0);
MPRINT(SINGLESORT_ADJ): ods output parameterestimates=param1 fitstatistics=fitresult
OutputStatistics=residual;
MPRINT(SINGLESORT_ADJ): quit;
WARNING: Output 'OutputStatistics' was not created. Make sure that the output object name,
label, or path is spelled correctly. Also, verify that the appropriate procedure
options are used to produce the requested output object. For example, verify that the
NOPRINT option is not used.
WARNING: Output 'fitstatistics' was not created. Make sure that the output object name, label,
or path is spelled correctly. Also, verify that the appropriate procedure options are
used to produce the requested output object. For example, verify that the NOPRINT
option is not used.
MPRINT(SINGLESORT_ADJ): data param1;
MPRINT(SINGLESORT_ADJ): set param1;
MPRINT(SINGLESORT_ADJ): type='CAPM Alpha';
MPRINT(SINGLESORT_ADJ): if parameter='a';
MPRINT(SINGLESORT_ADJ): if parameter='b1';
MPRINT(SINGLESORT_ADJ): run;
MLOGIC(SINGLESORT_ADJ): Ending execution.

Trusted Advisor
Posts: 1,607

Re: SAS Macro issue

You have requested that the OUTPUTSTATISTICS table from PROC MODEL be stored in a dataset named residual. However, there is no such table named OUTPUTSTATISTICS from PROC MODEL. See http://documentation.sas.com/?cdcId=etscdc&cdcVersion=14.2&docsetId=etsug&docsetTarget=etsug_model_s...

SAS Super FREQ
Posts: 682

Re: SAS Macro issue

Hi

 

In your code the name of the output table is the same as the name of the input table in Proc SQL. Because of this you get the warning:

WARNING: This CREATE TABLE statement recursively references the target table. A
         consequence of this is a possible data integrity problem.

 

Considering the ODS OUTPUT statement, check whether the Proc actually creates the output object you are looking for, you can use the ODS TRACE ON/OFF statement to check this, see example below:

ods trace on;
ods output moments=xmoments;

proc univariate data=sashelp.class;
  var age;
run;
ods trace off;

Bruno

 

Contributor
Posts: 58

Re: SAS Macro issue

Hello Bruno
I have run with changing names. the first porblem solved but other are still there can you help in that;
WARNING: Output 'OutputStatistics' was not created. Make sure that the output object name,
label, or path is spelled correctly. Also, verify that the appropriate procedure
options are used to produce the requested output object. For example, verify that the
NOPRINT option is not used.
WARNING: Output 'fitstatistics' was not created. Make sure that the output object name, label,
or path is spelled correctly. Also, verify that the appropriate procedure options are
used to produce the requested output object. For example, verify that the NOPRINT
option is not used.
WARNING: Output 'OutputStatistics' was not created. Make sure that the output object name,
label, or path is spelled correctly. Also, verify that the appropriate procedure
options are used to produce the requested output object. For example, verify that the
NOPRINT option is not used.
WARNING: Output 'fitstatistics' was not created. Make sure that the output object name, label,
or path is spelled correctly. Also, verify that the appropriate procedure options are
used to produce the requested output object. For example, verify that the NOPRINT
option is not used.


Thanks
SAS Super FREQ
Posts: 682

Re: SAS Macro issue

hi

 

Add the ODS TRACE ON; and ODS TRACE OFF; statements to see which output objects are created.

 

Bruno

Super User
Posts: 17,749

Re: SAS Macro issue

Here's a quick write up on 'ODS' tables that may help you understand why your code is incorrect.

 

I'm guessing that this isn't your code as well? Perhaps you can ask the original author for assistance?

Contributor
Posts: 58

Re: SAS Macro issue

Thank you everyone. These procedures were enough help to spot and correct errors. 

 

Occasional Contributor
Posts: 11

Re: SAS Macro issue

[ Edited ]

Helloo,

I have the same problem since I am using approximetaly the same macro. Can you please tell me what have you corrected with your sas code.

I have  added the ods trace on

I have got:

 

ods trace on;
ods output moments=xmoments;

proc model data=sum_diff5;
by _name_ GROUP_1;
parms a; exogenous col1 ;
instruments / intonly;
col1=a;
fit col1 / gmm kernel=(bart, 4, 0);
ods output parameterestimates=pa0 fitstatistics=fitresult OutputStatistics=residual;
quit;
ods trace off;

 

I have got : 

 


Output Added:
-------------
Name: ModSummary
Label: Variable Counts
Template: ets.model.ModSummary
Path: Model.ModSum.ModSummary
-------------

Output Added:
-------------
Name: ModVars
Label: Model Variables
Template: ets.model.ModVars
Path: Model.ModSum.ModVars
-------------

Output Added:
-------------
Name: Equations
Label: The Equation to Estimate is
Template: ets.model.Equations
Path: Model.ModSum.Equations
-------------

Output Added:
-------------
Name: ConvergenceStatus
Label: Convergence Status
Template: ets.model.ConvergenceStatus
Path: Model.ByGroup2.GMM.ConvergenceStatus
-------------

Output Added:
-------------
Name: DatasetOptions
Label: Data Set Options
Template: ets.model.DatasetOptions
Path: Model.ByGroup2.GMM.EstSum.DatasetOptions
-------------

Output Added:
-------------
Name: MinSummary
Label: Minimization Summary
Template: ets.model.MinSummary
Path: Model.ByGroup2.GMM.EstSum.MinSummary
-------------

Output Added:
-------------
Name: ConvCrit
Label: Final Convergence Criteria
Template: ets.model.ConvCrit
Path: Model.ByGroup2.GMM.EstSum.ConvCrit
-------------

Output Added:
-------------
Name: ObsUsed
Label: Observations Processed
Template: ets.model.ObsUsed
Path: Model.ByGroup2.GMM.EstSum.ObsUsed
-------------

Output Added:
-------------
Name: ResidSummary
Label: Nonlinear GMM Summary of Residual Errors
Template: ets.model.ResidSummary
Path: Model.ByGroup2.GMM.ResidSummary
-------------

Output Added:
-------------
Name: ParameterEstimates
Label: Nonlinear GMM Parameter Estimates
Template: ets.model.ParameterEstimates
Path: Model.ByGroup2.GMM.ParameterEstimates
-------------

Output Added:
-------------
Name: EstSummaryStats
Label: Estimation Summary Statistics
Template: ets.model.EstSummaryStats
Path: Model.ByGroup2.GMM.EstSummaryStats
-------------

Output Added:
-------------
Name: GMMTestStats
Label: GMM Test Statistics
Template: ets.model.GMMTestStats
Path: Model.ByGroup2.GMM.GMMTestStats
-------------

Output Added:
-------------
Name: DiagnosticsPanel
Label: Panel 1
Template: ETS.Model.Graphics.DiagnosticsPanel
Path: Model.ByGroup2.GMM.FitDiagnosticsPlots.DiagnosticsPanel
-------------

Output Added:
-------------
Name: DiagnosticsPanel
Label: Panel 2
Template: ETS.Model.Graphics.DiagnosticsPanel
Path: Model.ByGroup2.GMM.FitDiagnosticsPlots.DiagnosticsPanel

 

I think the problem is that I didn t find the fitstatistics and OutputStatistics within the log . How can I know what is the equivilant of these two to get the fitresult and residual ??

 

Thank youuuu

Ask a Question
Discussion stats
  • 11 replies
  • 361 views
  • 2 likes
  • 5 in conversation