DBMS Code Error

Reply
Frequent Contributor
Posts: 140

DBMS Code Error

Hi,

I have this part of my code below but each time I try orun I get an error around dbms = excel. Anyone know what has been coding wrong?

My code -

proc import datafile = "P:\Mortgage Finance\%nrstr(A&TP)\Mortgage NPV Model\CRDM\CRDM flow modelV.1.xlsx"

            out =Hedging_dataset

            dbms = excel

            replace;

   sheet = " ";

run;

%macro Import(path = ,outputdataset = , sheetname = );

proc import datafile = "&path" out = &outputdataset. dbms = excel replace;

sheet = "&sheetname";

run;

%mend;

%import(path= P:\Mortgage Finance\%nrstr(A&TP)\Mortgage NPV Model\CRDM\CRDM flow modelV.1.xlsx outputdataset = tab1 ,sheetname =BO data – Allocations)

%import(path= P:\Mortgage Finance\%nrstr(A&TP)\Mortgage NPV Model\CRDM\CRDM flow modelV.1.xlsx outputdataset = tab2 ,sheetname =BO data – Completions)

%import(path= P:\Mortgage Finance\%nrstr(A&TP)\Mortgage NPV Model\CRDM\CRDM flow modelV.1.xlsx outputdataset = tab3 ,sheetname =Hedging Assumptions 1)

%import(path= P:\Mortgage Finance\%nrstr(A&TP)\Mortgage NPV Model\CRDM\CRDM flow modelV.1.xlsx outputdataset = tab4 sheetname =Hedging Assumptions 2)

Error log -

 

742 %macro Import(path = ,outputdataset = , sheetname = );

743 proc import datafile = "&path" out = &outputdataset. dbms = excel replace;

744 sheet = "&sheetname";

745 run;

746 %mend;

747 %import(path= P:\Mortgage Finance\%nrstr(A&TP)\Mortgage NPV Model\CRDM\CRDM flow modelV.1.xlsx

747! outputdataset = tab1 ,sheetname =BO data – Allocations)

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

NOTE: The SAS System stopped processing this step because of errors.

NOTE: Line generated by the invoked macro "IMPORT".

1 proc import datafile = "&path" out = &outputdataset. dbms = excel replace; sheet = "&sheetname";

-

22

76

1 ! run;

ERROR 22-322: Syntax error, expecting one of the following: DATAFILE, DATATABLE, DBMS, FILE, OUT,

TABLE.

ERROR 76-322: Syntax error, statement will be ignored.

748 %import(path= P:\Mortgage Finance\%nrstr(A&TP)\Mortgage NPV Model\CRDM\CRDM flow modelV.1.xlsx

748! outputdataset = tab2 ,sheetname =BO data – Completions)

 

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

NOTE: The SAS System stopped processing this step because of errors.

NOTE: Line generated by the invoked macro "IMPORT".

1 proc import datafile = "&path" out = &outputdataset. dbms = excel replace; sheet = "&sheetname";

-

22

76

1 ! run;

ERROR 22-322: Syntax error, expecting one of the following: DATAFILE, DATATABLE, DBMS, FILE, OUT,

TABLE.

ERROR 76-322: Syntax error, statement will be ignored.

749 %import(path= P:\Mortgage Finance\%nrstr(A&TP)\Mortgage NPV Model\CRDM\CRDM flow modelV.1.xlsx

749! outputdataset = tab3 ,sheetname =Hedging Assumptions 1)

 

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

NOTE: The SAS System stopped processing this step because of errors.

NOTE: Line generated by the invoked macro "IMPORT".

1 proc import datafile = "&path" out = &outputdataset. dbms = excel replace; sheet = "&sheetname";

-

22

76

1 ! run;

ERROR 22-322: Syntax error, expecting one of the following: DATAFILE, DATATABLE, DBMS, FILE, OUT,

TABLE.

ERROR 76-322: Syntax error, statement will be ignored.

750 %import(path= P:\Mortgage Finance\%nrstr(A&TP)\Mortgage NPV Model\CRDM\CRDM flow modelV.1.xlsx

750! outputdataset = tab4 sheetname =Hedging Assumptions 2)

 

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

NOTE: The SAS System stopped processing this step because of errors.

NOTE: Line generated by the invoked macro "IMPORT".

1 proc import datafile = "&path" out = &outputdataset. dbms = excel replace; sheet = "&sheetname";

-

22

76

1 ! run;

ERROR 22-322: Syntax error, expecting one of the following: DATAFILE, DATATABLE, DBMS, FILE, OUT,

TABLE.

ERROR 76-322: Syntax error, statement will be ignored.


Super User
Super User
Posts: 7,401

Re: DBMS Code Error

Missing a comma after xlsx:

%import(path= P:\Mortgage Finance\%nrstr(A&TP)\Mortgage NPV Model\CRDM\CRDM flow modelV.1.xlsx, outputdataset = tab1 ,sheetname =BO data – Allocations);

                                                                                                                                                                                             ^


Also, close commands/calls with a colon, it may not be necessary but its easier to read.

New Contributor
Posts: 2

Re: DBMS Code Error

I get this same error but my code is slightly different from the original poster's and I can't figure out what I'm supposed to change. Any help would be much appreciated! 

 

%let subdir = C:\Documents\Research\MY MS\Active\Study 1\Analyses\Data\Oct 2016\4CM\;
filename dir "&subdir.*.xlsx ";
data new;
length filename fname $ 200;
infile dir eof=last filename=fname;
input ;
last: filename=fname;
run;
proc print data=new; run;
proc sort data=new nodupkey;
by filename;
run;
data null;
set new;
call symputx(cats('filename',_n_),filename);
call symputx(cats('dsn',_n_),compress(scan(filename,-2,'\.'), ,'ka'));
call symputx('nobs',_n_);
run;
proc print data=null; run;

 

%put &nobs.;
%macro import;
%do i=1 %to &nobs;
proc import datafile="&&filename&i" out=&&dsn&i dbms=excel replace;
getnames=yes;
mixed=yes;
run;
%end;
%mend import;
%import

 

 

NOTE 137-205: Line generated by the invoked macro "IMPORT".
29 proc import datafile="&&filename&i" out=&&dsn&i dbms=excel replace; getnames=yes; mixed=yes;
-
22
29 ! run;
ERROR 22-322: Syntax error, expecting one of the following: DATAFILE, DATATABLE, DBMS, FILE, OUT,
TABLE.

NOTE: Line generated by the invoked macro "IMPORT".
29 proc import datafile="&&filename&i" out=&&dsn&i dbms=excel replace; getnames=yes; mixed=yes;
-
76
29 ! run;
ERROR 76-322: Syntax error, statement will be ignored.

 

Super User
Posts: 5,256

Re: DBMS Code Error

First, f
Don't hijack an existing old thread, espacially when you have a different issue.
As with all macro debugging:
- Do your code work as a non macro version?
- Use symbolgen and mprint to see what's going on in your macro execution / evaluation.
Data never sleeps
Ask a Question
Discussion stats
  • 3 replies
  • 303 views
  • 0 likes
  • 4 in conversation