Thank you very much for your help, @SASKiwi .
It seems that I no longer face the same problem, but also raising some other errors, I am wondering what is the problem there.
Here is the log:
43 options compress=yes reuse=yes;
44
45 %macro ImportAndTranspose(
46 File=
47 , StartSheet=
48 , EndSheet=
49 );
50
51 %local i;
52
53 %do i = &StartSheet. %to &EndSheet.;
54
55 %if &i=1 %then %do;
56
57 proc import datafile= "&File."
2 The SAS System 14:42 Sunday, January 3, 2021
58 out= sheet1
59 dbms= xlsx
60 replace;
61 range= "Sheet1$A:X";
62 getnames= yes;
63 run;
64
65 proc sort data= sheet1;
66 by Type;
67 run;
68 %end;
69
70 %else %if &i= &EndSheet. %then %do;
71 proc import datafile= "&File."
72 out= sheet&i.
73 dbms= xlsx
74 replace;
75 range= "Sheet&i.$A:AG";
76 getnames= yes;
77 run;
78
79 proc sort data= sheet&i.;
80 by Type;
81 run;
82
83 proc transpose data= sheet&i.
84 out= sheet&i._out(rename=(COL1=s&i. _NAME_=Year) drop=_label_);
85 by Type;
86 VAR '1988'N - '2019'N;
87 run;
88 proc print data=sheet&i._out;
89 run;
90
91 data sheet&i._outx;
92 set sheet&i._out;
93 if s&i. in: ('NA', '$$') then s&i. =".";
94 run;
95
96 %end;
97
98
99 %else %if (&i ne 1) and (&i ne Endsheet.) %then %do;
100 proc import datafile= "&File."
101 out= sheet&i.
102 dbms= xlsx
103 replace;
104 range= "Sheet&i.$A:AG";
105 getnames= yes;
106 run;
107
108 proc sort data= sheet&i.;
109 by Type;
110 run;
111
112 proc transpose data= sheet&i.
113 out= sheet&i._out(rename=(COL1=s&i. _NAME_=Year) drop=_label_);
114 by Type;
115 VAR '1988'N - '2019'N;
3 The SAS System 14:42 Sunday, January 3, 2021
116 run;
117 proc print data=sheet&i._out;
118 run;
119
120 DATA sheet&i._outx;
121 set sheet&i._out;
122
123 if s&i. not in: ('NA', '$$') then s&i.2=input(s&i., 32.);
124 drop s&i.;
125 run;
126 %end;
127
128
129
130 %end;
131
132 %mend;
133
134 *Replicate all file in one folder;
135 data _null_;
136 length fref $8 fname $200;
137 did = filename(fref,'C:\Users\pnguyen\Desktop\New folder');
138 did = dopen(fref);
139 do i = 1 to dnum(did);
140 fname = dread(did,i);
141 cmd=cats(%ImportAndTranspose(File=',
142 strip(fname),',startsheet=1,endsheet=34);');
NOTE: Line generated by the invoked macro "IMPORTANDTRANSPOSE".
142 proc import datafile= "&File." out= sheet1 dbms= xlsx replace;
______ ___ _______
388 22 388
202 76
ERROR 388-185: Expecting an arithmetic operator.
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, ><, >=, AND, EQ, GE,
GT, IN, LE, LT, MAX, MIN, NE, NG, NL, NOTIN, OR, ^=, |, ||, ~=.
ERROR 202-322: The option or parameter is not recognized and will be ignored.
ERROR 76-322: Syntax error, statement will be ignored.
142 ! proc import datafile= "&File." out= sheet1 dbms= xlsx replace;
____
22
142 ! range= "Sheet1$A:X"; getnames= yes; run; proc sort data= sheet1; by Type;
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, ',', -, /, <, <=, <>, =, >, ><, >=, AND, EQ, GE,
GT, IN, LE, LT, MAX, MIN, NE, NG, NL, NOTIN, OR, [, ^=, {, |, ||, ~=.
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
ERROR: File WORK.SHEET1.DATA does not exist.
4 The SAS System 14:42 Sunday, January 3, 2021
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
ERROR: Physical file does not exist, C:\WINDOWS\system32\, strip(fname),.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
ERROR: File WORK.SHEET2.DATA does not exist.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
ERROR: File WORK.SHEET2.DATA does not exist.
And a further concern is: can I ask where I can write
put cmd = ;
to check to the syntax in case needed ?
Many thanks and warmest regards.
... View more
