Running SAS code in enterprise guide vs PC sas reading Excel spread sheet with blanks in col names

StevePeeFromAZ — Tue, 10 Oct 2017 18:00:08 GMT

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?

Re: Running SAS code in enterprise guide vs PC sas reading Excel spread sheet with blanks in col nam

Reeza — Tue, 10 Oct 2017 18:01:22 GMT

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?

Re: Running SAS code in enterprise guide vs PC sas reading Excel spread sheet with blanks in col nam

StevePeeFromAZ — Wed, 11 Oct 2017 15:51:39 GMT

Hi Reeza, I'm new to this community, is there a way to include the code/log without adding text body?

Re: Running SAS code in enterprise guide vs PC sas reading Excel spread sheet with blanks in col nam

Tom — Wed, 11 Oct 2017 16:27:55 GMT

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.

Re: Running SAS code in enterprise guide vs PC sas reading Excel spread sheet with blanks in col nam

StevePeeFromAZ — Wed, 11 Oct 2017 18:46:05 GMT

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

topic Re: Running SAS code in enterprise guide vs PC sas reading Excel spread sheet with blanks in col nam in SAS Enterprise Guide

Running SAS code in enterprise guide vs PC sas reading Excel spread sheet with blanks in col names

Re: Running SAS code in enterprise guide vs PC sas reading Excel spread sheet with blanks in col nam

Re: Running SAS code in enterprise guide vs PC sas reading Excel spread sheet with blanks in col nam

Re: Running SAS code in enterprise guide vs PC sas reading Excel spread sheet with blanks in col nam

Re: Running SAS code in enterprise guide vs PC sas reading Excel spread sheet with blanks in col nam