Tom/Reeza here is the log and code the user sent me. Thanks for you response. Options validvarname=V7;
*options noxwait;
%let OY=OY17;
%let dir=\\srp.gov\50000\SupplyandTrading\Term_Trading\Power\Hydro\APA\APA NWM\CAW Dynamic Signal\Motoring 6yr\&OY;
%macro ReadXls (dir=);
/*DOS command to get a list of all data files programs */
filename mylist pipe "%str(dir %"&dir%" /B) " lrecl=5000;
/*put the list of files into a data set for macro processing*/
data mylist;
infile mylist truncover;
input filen $50.;
put _infile_;
run;
proc sql noprint;
select count(filen) into :cntfile from mylist;
%if &cntfile>=1 %then %do;
select filen into :filen1-:filen%left(&cntfile)
from mylist;
%end;
quit;
%do i=1 %to &cntfile;
libname excellib excel "&dir.\&&filen&i";
proc sql noprint;
create table sheetname as
select tranwrd(memname, "''", "'") as sheetname
from sashelp.vstabvw
where libname="EXCELLIB";
select count(DISTINCT sheetname) into :cnt_sht
from sheetname;
select DISTINCT sheetname into :sheet1 - :sheet%left(&cnt_sht)
from sheetname;
quit;
%do j=1 %to &cnt_sht;
proc import datafile="&dir.\&&filen&i"
out=sheet&j replace;
sheet="&&sheet&j";
getnames=yes;
mixed=yes;
run;
data sheet&j;
length _excelfilename $100 _sheetname $32 Schedule_Name_1 $10;
format Schedule_Name_1 $10.;
set sheet&j;
excelfilename="&&filen&i";
sheetname="&&sheet&j";
run;
proc append base=master data=sheet&j force;
run;
%end;
libname excellib clear;
%end;
%mend ReadXls;
%readxls (dir=&dir)
data cawmotor(keep= Date HE01-HE24 Year Month Day);
set Master;
if Day_01__HE_01=. then delete;
Date1=Schedule_Name_1;
HE01=Day_01__HE_01;
HE02=Day_01__HE_02;
HE03=Day_01__HE_03;
HE04=Day_01__HE_04;
HE05=Day_01__HE_05;
HE06=Day_01__HE_06;
HE07=Day_01__HE_07;
HE08=Day_01__HE_08;
HE09=Day_01__HE_09;
HE10=Day_01__HE_10;
HE11=Day_01__HE_11;
HE12=Day_01__HE_12;
HE13=Day_01__HE_13;
HE14=Day_01__HE_14;
HE15=Day_01__HE_15;
HE16=Day_01__HE_16;
HE17=Day_01__HE_17;
HE18=Day_01__HE_18;
HE19=Day_01__HE_19;
HE20=Day_01__HE_20;
HE21=Day_01__HE_21;
HE22=Day_01__HE_22;
HE23=Day_01__HE_23;
HE24=Day_01__HE_24;
if Date1 in('Totals:' 'HE') then delete;
if Date1 = ' ' then delete;
Date=input(date1, ANYDTDTE11.);
format date date9.;
/* the next line eliminates duplicate dates with 0 schedules when non-zero is valid */
if sum(he07,he08,he09,he10,he11,he12,he13,he14,he15,he16,he17,he18,he19,he20,he21,he22,he23)=0 then delete;
Year=year(date);
Month=month(date);
Day=day(date);
run;
proc sort nodupkey data=cawmotor; by date; run; 1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='CAWCD Import Motoring XLSX';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='S:\SupplyandTrading\Term_Trading\Power\Analysis\Bill''s SAS Enterprise Guide Programs\Hoover
5 ! Capacity Available.egp';
6 %LET _CLIENTPROJECTNAME='Hoover Capacity Available.egp';
7 %LET _SASPROGRAMFILE=;
8
9 ODS _ALL_ CLOSE;
10 OPTIONS DEV=ACTIVEX;
11 GOPTIONS XPIXELS=0 YPIXELS=0;
12 FILENAME EGSR TEMP;
13 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
14 STYLE=HtmlBlue
15 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
16 NOGTITLE
17 NOGFOOTNOTE
18 GPATH=&sasworklocation
19 ENCODING=UTF8
20 options(rolap="on")
21 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
22
23 GOPTIONS ACCESSIBLE;
24
25 Options validvarname=V7;
26 *options noxwait;
27 %let OY=OY17;
28 %let dir=\\srp.gov\50000\SupplyandTrading\Term_Trading\Power\Hydro\APA\APA NWM\CAW Dynamic Signal\Motoring 6yr\&OY;
29 %macro ReadXls (dir=);
30 /*DOS command to get a list of all data files programs */
31 filename mylist pipe "%str(dir %"&dir%" /B) " lrecl=5000;
32
33 /*put the list of files into a data set for macro processing*/
34 data mylist;
35 infile mylist truncover;
36 input filen $50.;
37 put _infile_;
38 run;
39 proc sql noprint;
40 select count(filen) into :cntfile from mylist;
41 %if &cntfile>=1 %then %do;
42 select filen into :filen1-:filen%left(&cntfile)
43 from mylist;
44 %end;
45 quit;
46
47 %do i=1 %to &cntfile;
48 libname excellib excel "&dir.\&&filen&i";
49 proc sql noprint;
50 create table sheetname as
51 select tranwrd(memname, "''", "'") as sheetname
52 from sashelp.vstabvw
53 where libname="EXCELLIB";
54 select count(DISTINCT sheetname) into :cnt_sht
55 from sheetname;
56 select DISTINCT sheetname into :sheet1 - :sheet%left(&cnt_sht)
2 The SAS System 08:59 Monday, October 9, 2017
57 from sheetname;
58 quit;
59
60 %do j=1 %to &cnt_sht;
61 proc import datafile="&dir.\&&filen&i"
62 out=sheet&j replace;
63 sheet="&&sheet&j";
64 getnames=yes;
65 mixed=yes;
66 run;
67
68 data sheet&j;
69 length _excelfilename $100 _sheetname $32 Schedule_Name_1 $10;
70 format Schedule_Name_1 $10.;
71 set sheet&j;
72 excelfilename="&&filen&i";
73 sheetname="&&sheet&j";
74 run;
75 proc append base=master data=sheet&j force;
76 run;
77 %end;
78 libname excellib clear;
79
80 %end;
81 %mend ReadXls;
82
83 %readxls (dir=&dir)
NOTE: The infile MYLIST is:
Unnamed Pipe Access Device,
PROCESS=dir "\\srp.gov\50000\SupplyandTrading\Term_Trading\Power\Hydro\APA\APA NWM\CAW Dynamic Signal\Motoring 6yr\OY17" /B,
RECFM=V,LRECL=5000
CAWCD Motor Payback 08-2017.xlsx
CAWCD Motor Payback 09-2017.xlsx
NOTE: 2 records were read from the infile MYLIST.
The minimum record length was 32.
The maximum record length was 32.
NOTE: The data set WORK.MYLIST has 2 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.06 seconds
cpu time 0.00 seconds
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
NOTE: Libref EXCELLIB was successfully assigned as follows:
Engine: EXCEL
Physical Name: \\srp.gov\50000\SupplyandTrading\Term_Trading\Power\Hydro\APA\APA NWM\CAW Dynamic Signal\Motoring
6yr\OY17\CAWCD Motor Payback 08-2017.xlsx
NOTE: The query as specified involves ordering by an item that doesn't appear in its SELECT clause.
NOTE: Table WORK.SHEETNAME created, with 1 rows and 1 columns.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
3 The SAS System 08:59 Monday, October 9, 2017
cpu time 0.01 seconds
1 The SAS System 12:22 Tuesday, October 10, 2017
NOTE: Data source is connected in READ ONLY mode.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.16 seconds
cpu time 0.15 seconds
NOTE: WORK.SHEET1 data set was successfully created.
NOTE: The data set WORK.SHEET1 has 50 observations and 255 variables.
NOTE: Variable _excelfilename is uninitialized.
NOTE: Variable _sheetname is uninitialized.
NOTE: There were 50 observations read from the data set WORK.SHEET1.
NOTE: The data set WORK.SHEET1 has 50 observations and 259 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds
NOTE: Appending WORK.SHEET1 to WORK.MASTER.
ERROR: The value Schedule Name 1 is not a valid SAS name.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE APPEND used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
NOTE: Libref EXCELLIB has been deassigned.
NOTE: Libref EXCELLIB was successfully assigned as follows:
Engine: EXCEL
Physical Name: \\srp.gov\50000\SupplyandTrading\Term_Trading\Power\Hydro\APA\APA NWM\CAW Dynamic Signal\Motoring
6yr\OY17\CAWCD Motor Payback 09-2017.xlsx
NOTE: The query as specified involves ordering by an item that doesn't appear in its SELECT clause.
NOTE: Table WORK.SHEETNAME created, with 1 rows and 1 columns.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
1 The SAS System 12:22 Tuesday, October 10, 2017
NOTE: Data source is connected in READ ONLY mode.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.16 seconds
cpu time 0.15 seconds
NOTE: WORK.SHEET1 data set was successfully created.
NOTE: The data set WORK.SHEET1 has 50 observations and 255 variables.
NOTE: Variable _excelfilename is uninitialized.
NOTE: Variable _sheetname is uninitialized.
NOTE: There were 50 observations read from the data set WORK.SHEET1.
NOTE: The data set WORK.SHEET1 has 50 observations and 259 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
4 The SAS System 08:59 Monday, October 9, 2017
cpu time 0.00 seconds
NOTE: Appending WORK.SHEET1 to WORK.MASTER.
ERROR: The value Schedule Name 1 is not a valid SAS name.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE APPEND used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
NOTE: Libref EXCELLIB has been deassigned.
84
85 data cawmotor(keep= Date HE01-HE24 Year Month Day);
86 set Master;
ERROR: The value Schedule Name 1 is not a valid SAS name.
87 if Day_01__HE_01=. then delete;
88 Date1=Schedule_Name_1;
89 HE01=Day_01__HE_01;
90 HE02=Day_01__HE_02;
91 HE03=Day_01__HE_03;
92 HE04=Day_01__HE_04;
93 HE05=Day_01__HE_05;
94 HE06=Day_01__HE_06;
95 HE07=Day_01__HE_07;
96 HE08=Day_01__HE_08;
97 HE09=Day_01__HE_09;
98 HE10=Day_01__HE_10;
99 HE11=Day_01__HE_11;
100 HE12=Day_01__HE_12;
101 HE13=Day_01__HE_13;
102 HE14=Day_01__HE_14;
103 HE15=Day_01__HE_15;
104 HE16=Day_01__HE_16;
105 HE17=Day_01__HE_17;
106 HE18=Day_01__HE_18;
107 HE19=Day_01__HE_19;
108 HE20=Day_01__HE_20;
109 HE21=Day_01__HE_21;
110 HE22=Day_01__HE_22;
111 HE23=Day_01__HE_23;
112 HE24=Day_01__HE_24;
113 if Date1 in('Totals:' 'HE') then delete;
114 if Date1 = ' ' then delete;
115 Date=input(date1, ANYDTDTE11.);
116 format date date9.;
117 /* the next line eliminates duplicate dates with 0 schedules when non-zero is valid */
118 if sum(he07,he08,he09,he10,he11,he12,he13,he14,he15,he16,he17,he18,he19,he20,he21,he22,he23)=0 then delete;
119 Year=year(date);
120 Month=month(date);
121 Day=day(date);
122 run;
NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
113:5 114:13
NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
5 The SAS System 08:59 Monday, October 9, 2017
115:13
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.CAWMOTOR may be incomplete. When this step was stopped there were 0 observations and 28 variables.
WARNING: Data set WORK.CAWMOTOR was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
123
124 proc sort nodupkey data=cawmotor; by date; run;
NOTE: Input data set is already sorted, no sorting done.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
125
126
127 GOPTIONS NOACCESSIBLE;
128 %LET _CLIENTTASKLABEL=;
129 %LET _CLIENTPROCESSFLOWNAME=;
130 %LET _CLIENTPROJECTPATH=;
131 %LET _CLIENTPROJECTNAME=;
132 %LET _SASPROGRAMFILE=;
133
134 ;*';*";*/;quit;run;
135 ODS _ALL_ CLOSE;
136
137
138 QUIT; RUN;
139
... View more
