Hi @andreas_lds , @Shmuel , another question is about the ERROR existing when running the macro called "All positional parameters must precede keyword parameters", could you please tell me how to sort it out?
Here is my code:
options compress=yes reuse=yes;
%macro ImportAndTranspose(
File=
, StartSheet=
, EndSheet=
);
%local i;
%do i = &StartSheet. %to &EndSheet.;
%if &i=1 %then %do;
proc import datafile= "&File."
out= sheet1
dbms= xlsx
replace;
range= "Sheet1$A:X";
getnames= yes;
run;
proc sort data= sheet1;
by Type;
run;
%end;
%else %if &i= &EndSheet. %then %do;
proc import datafile= "&File."
out= sheet&i.
dbms= xlsx
replace;
range= "Sheet&i.$A:AG";
getnames= yes;
run;
proc sort data= sheet&i.;
by Type;
run;
proc transpose data= sheet&i.
out= sheet&i._out(rename=(COL1=s&i. _NAME_=Year) drop=_label_);
by Type;
VAR '1988'N - '2019'N;
run;
proc print data=sheet&i._out;
run;
data sheet&i._outx;
set sheet&i._out;
if s&i. in: ('NA', '$$') then s&i. =".";
run;
%end;
%else %if (&i ne 1) and (&i ne Endsheet.) %then %do;
proc import datafile= "&File."
out= sheet&i.
dbms= xlsx
replace;
range= "Sheet&i.$A:AG";
getnames= yes;
run;
proc sort data= sheet&i.;
by Type;
run;
proc transpose data= sheet&i.
out= sheet&i._out(rename=(COL1=s&i. _NAME_=Year) drop=_label_);
by Type;
VAR '1988'N - '2019'N;
run;
proc print data=sheet&i._out;
run;
DATA sheet&i._outx;
set sheet&i._out;
if s&i. not in: ('NA', '$$') then s&i.2=input(s&i., 32.);
drop s&i.;
run;
%end;
%end;
%mend;
*Replicate all file in one folder;
data _null_;
length fref $8 fname $200;
did = filename(fref,'C:\Users\pnguyen\Desktop\New folder');
did = dopen(fref);
do i = 1 to dnum(did);
fname = dread(did,i);
cmd=catx(',', '%ImportAndTranspose(File=',
strip(fname), 'startsheet=1,endsheet=34);');
call execute(cmd);
end;
did = dclose(did);
did = filename(fref);
keep fname;
run;
The log is as below:
1 The SAS System 14:42 Sunday, January 3, 2021
1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='replicate code for all sheets -included s34 _inc filenamer';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='C:\Users\pnguyen\OneDrive - Massey University\SAS\29_Dec_redo_all.egp';
6 %LET _CLIENTPROJECTPATHHOST='IT082760';
7 %LET _CLIENTPROJECTNAME='29_Dec_redo_all.egp';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=SVG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 %macro HTML5AccessibleGraphSupported;
15 %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
16 %mend;
17 FILENAME EGHTML TEMP;
18 ODS HTML5(ID=EGHTML) FILE=EGHTML
19 OPTIONS(BITMAP_MODE='INLINE')
20 %HTML5AccessibleGraphSupported
21 ENCODING='utf-8'
22 STYLE=HtmlBlue
23 NOGTITLE
24 NOGFOOTNOTE
25 GPATH=&sasworklocation
26 ;
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
27
28 options compress=yes reuse=yes;
29
30 %macro ImportAndTranspose(
31 File=
32 , StartSheet=
33 , EndSheet=
34 );
35
36 %local i;
37
38 %do i = &StartSheet. %to &EndSheet.;
39
40 %if &i=1 %then %do;
41
42 proc import datafile= "&File."
43 out= sheet1
44 dbms= xlsx
45 replace;
46 range= "Sheet1$A:X";
47 getnames= yes;
48 run;
49
50 proc sort data= sheet1;
51 by Type;
52 run;
53 %end;
54
55 %else %if &i= &EndSheet. %then %do;
56 proc import datafile= "&File."
57 out= sheet&i.
2 The SAS System 14:42 Sunday, January 3, 2021
58 dbms= xlsx
59 replace;
60 range= "Sheet&i.$A:AG";
61 getnames= yes;
62 run;
63
64 proc sort data= sheet&i.;
65 by Type;
66 run;
67
68 proc transpose data= sheet&i.
69 out= sheet&i._out(rename=(COL1=s&i. _NAME_=Year) drop=_label_);
70 by Type;
71 VAR '1988'N - '2019'N;
72 run;
73 proc print data=sheet&i._out;
74 run;
75
76 data sheet&i._outx;
77 set sheet&i._out;
78 if s&i. in: ('NA', '$$') then s&i. =".";
79 run;
80
81 %end;
82
83
84 %else %if (&i ne 1) and (&i ne Endsheet.) %then %do;
85 proc import datafile= "&File."
86 out= sheet&i.
87 dbms= xlsx
88 replace;
89 range= "Sheet&i.$A:AG";
90 getnames= yes;
91 run;
92
93 proc sort data= sheet&i.;
94 by Type;
95 run;
96
97 proc transpose data= sheet&i.
98 out= sheet&i._out(rename=(COL1=s&i. _NAME_=Year) drop=_label_);
99 by Type;
100 VAR '1988'N - '2019'N;
101 run;
102 proc print data=sheet&i._out;
103 run;
104
105 DATA sheet&i._outx;
106 set sheet&i._out;
107
108 if s&i. not in: ('NA', '$$') then s&i.2=input(s&i., 32.);
109 drop s&i.;
110 run;
111 %end;
112
113
114
115 %end;
3 The SAS System 14:42 Sunday, January 3, 2021
116
117 %mend;
118
119 *Replicate all file in one folder;
120 data _null_;
121 length fref $8 fname $200;
122 did = filename(fref,'C:\Users\pnguyen\Desktop\New folder');
123 did = dopen(fref);
124 do i = 1 to dnum(did);
125 fname = dread(did,i);
126 cmd=catx(',', '%ImportAndTranspose(File=',
127 strip(fname), 'startsheet=1,endsheet=34);');
128 call execute(cmd);
129 end;
130 did = dclose(did);
131 did = filename(fref);
132 keep fname;
133 run;
ERROR: All positional parameters must precede keyword parameters.
ERROR: All positional parameters must precede keyword parameters.
ERROR: All positional parameters must precede keyword parameters.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
NOTE: CALL EXECUTE generated line.
NOTE: Line generated by the CALL EXECUTE routine.
1 +startsheet=1,endsheet=34);
__________
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
NOTE: Line generated by the CALL EXECUTE routine.
2 +startsheet=1,endsheet=34);
__________
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
NOTE: Line generated by the CALL EXECUTE routine.
3 +startsheet=1,endsheet=34);
__________
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
134
135 %LET _CLIENTTASKLABEL=;
136 %LET _CLIENTPROCESSFLOWNAME=;
137 %LET _CLIENTPROJECTPATH=;
138 %LET _CLIENTPROJECTPATHHOST=;
139 %LET _CLIENTPROJECTNAME=;
140 %LET _SASPROGRAMFILE=;
141 %LET _SASPROGRAMFILEHOST=;
4 The SAS System 14:42 Sunday, January 3, 2021
142
143 ;*';*";*/;quit;run;
144 ODS _ALL_ CLOSE;
145
146
147 QUIT; RUN;
148
While I run the code of reading all filenames only, the code work successfully:
data filenames;
length fref $8 fname $200;
did = filename(fref,'H:\');
did = dopen(fref);
do i = 1 to dnum(did);
fname = dread(did,i);
output;
end;
did = dclose(did);
did = filename(fref);
keep fname;
run;
So I thought the problem occurring due to the added function called (execute) or else.
Many thanks and cheers!
... View more
