My client is converting from PC SAS to EG 7.1 and is trying to run a job that imports excel spread sheets (one for each month) that have blanks and commas in the column names (i.e. "Month12, HE12") Code runs fine in PC SAS and converted var names to underscores but fails to convert in EG. Had user add "Options validvarname=V7;" but still didn't work. Suggestions from the community?
Options validvarname=V7;
That should work to automatically add the underscores.
Can you include the code, log and a proc contents on the output that shows the issue?
Are they running code to call PROC IMPORT?
Or did they use the import tool of Enterrprise Guide? That tool will not call PROC IMPORT. Instead it will convert the spreadsheet to text, upload the text and run a data step that it generates to read the text into a dataset.
To post text or code use one of the insert code button in the menu bar of the mini-editor.
I like to use the {i} button for data and logs and the running man icon for SAS code.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.