Still getting a new worksheet with a new range
log
1 The SAS System 20:19 Thursday, September 21, 2017 1 %_eg_hidenotesandsource; MPRINT(_EG_HIDENOTESANDSOURCE): options nonotes; MPRINT(_EG_HIDENOTESANDSOURCE): options nosource; SYMBOLGEN: Macro variable _EGNOTES resolves to NOTES MPRINT(_EG_RESTORENOTESANDSOURCE): options NOTES; SYMBOLGEN: Macro variable _EGSOURCE resolves to SOURCE MPRINT(_EG_RESTORENOTESANDSOURCE): options SOURCE; 5 %_eg_hidenotesandsource; MPRINT(_EG_HIDENOTESANDSOURCE): options nonotes; MPRINT(_EG_HIDENOTESANDSOURCE): options nosource; SYMBOLGEN: Macro variable SASWORKLOCATION resolves to "F:\SASWORK\_TD28392_ISS-SAS01_\Prc2/" SYMBOLGEN: Macro variable _EGNOTES resolves to NOTES MPRINT(_EG_RESTORENOTESANDSOURCE): options NOTES; SYMBOLGEN: Macro variable _EGSOURCE resolves to SOURCE MPRINT(_EG_RESTORENOTESANDSOURCE): options SOURCE; 31 32 libname out xlsx "Path\Template_test2.xlsx"; NOTE: Libref OUT was successfully assigned as follows: Engine: XLSX Physical Name: Path\Template_test2.xlsx 33 34 %macro create_yourdata; 35 %do i=1 %to 1;*change for max obs; 36 37 data yourdata_&i (keep=items); 38 set new3; 39 if flag=&i; 40 run; 41 42 /* proc export data=yourdata_&i*/ 43 /* file=output*/ 44 /* dbms=XLSX replace;*/ 45 /* Range='SAS_RANGE';*/ 46 /* run;*/ 47 48 /*proc sql;*/ 49 /*drop table out.SAS_RANGE;*/ 50 /*quit;*/ 51 52 data out.SAS_RANGE; 53 set yourdata_&i ; 54 run; 55 56 %end; 57 options mprint symbolgen; 58 %mend create_yourdata; 59 %create_yourdata; MPRINT(CREATE_YOURDATA): *change for max obs; SYMBOLGEN: Macro variable I resolves to 1 MPRINT(CREATE_YOURDATA): data yourdata_1 (keep=Items); MPRINT(CREATE_YOURDATA): set new3; SYMBOLGEN: Macro variable I resolves to 1 MPRINT(CREATE_YOURDATA): if flag=1; MPRINT(CREATE_YOURDATA): run; 2 The SAS System 20:19 Thursday, September 21, 2017 NOTE: There were 11133 observations read from the data set WORK.NEW3. NOTE: The data set WORK.YOURDATA_1 has 226 observations and 12 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds MPRINT(CREATE_YOURDATA): data out.SAS_RANGE; SYMBOLGEN: Macro variable I resolves to 1 MPRINT(CREATE_YOURDATA): set yourdata_1 ; MPRINT(CREATE_YOURDATA): run; WARNING: Some character data was lost during transcoding data: Data 5: 2012/13-2016/17: Value (£) of successful grant applications for 1st August 2012 to 31st July 2017 . NOTE: There were 226 observations read from the data set WORK.YOURDATA_1. NOTE: The data set OUT.SAS_RANGE has . observations and 12 variables. NOTE: The export data set has 226 observations and 12 variables. NOTE: DATA statement used (Total process time): real time 0.39 seconds cpu time 0.14 seconds MPRINT(CREATE_YOURDATA): options mprint symbolgen; 60 61 %_eg_hidenotesandsource; MPRINT(_EG_HIDENOTESANDSOURCE): options nonotes; MPRINT(_EG_HIDENOTESANDSOURCE): options nosource; SYMBOLGEN: Macro variable _EGNOTES resolves to NOTES MPRINT(_EG_RESTORENOTESANDSOURCE): options NOTES; SYMBOLGEN: Macro variable _EGSOURCE resolves to SOURCE MPRINT(_EG_RESTORENOTESANDSOURCE): options SOURCE; 76 77 78 %_eg_hidenotesandsource; MPRINT(_EG_HIDENOTESANDSOURCE): options nonotes; MPRINT(_EG_HIDENOTESANDSOURCE): options nosource; SYMBOLGEN: Macro variable _EGNOTES resolves to NOTES MPRINT(_EG_RESTORENOTESANDSOURCE): options NOTES; SYMBOLGEN: Macro variable _EGSOURCE resolves to SOURCE MPRINT(_EG_RESTORENOTESANDSOURCE): options SOURCE; 81
That's annoying as hell. This definitely used to work as of 9.3, I have a presentation where that's the exact method I used:
I would consider talking to SAS tech support regarding this issue.
EDIT: I tried this with SAS 9.4 TS1M3 with both DBMS=XLSX and PCFILES and it still didn't work either.
Thank you, that actually helps alot as I don't feel such a noobie
I'll speak to our SAS rep and see what they say.
Log when using sheet
1 %_eg_hidenotesandsource;
MPRINT(_EG_HIDENOTESANDSOURCE): options nonotes;
MPRINT(_EG_HIDENOTESANDSOURCE): options nosource;
SYMBOLGEN: Macro variable _EGNOTES resolves to NOTES
MPRINT(_EG_RESTORENOTESANDSOURCE): options NOTES;
SYMBOLGEN: Macro variable _EGSOURCE resolves to SOURCE
MPRINT(_EG_RESTORENOTESANDSOURCE): options SOURCE;
5 %_eg_hidenotesandsource;
MPRINT(_EG_HIDENOTESANDSOURCE): options nonotes;
MPRINT(_EG_HIDENOTESANDSOURCE): options nosource;
SYMBOLGEN: Macro variable SASWORKLOCATION resolves to "F:\SASWORK\_TD28392_ISS-SAS01_\Prc2/"
SYMBOLGEN: Macro variable _EGNOTES resolves to NOTES
MPRINT(_EG_RESTORENOTESANDSOURCE): options NOTES;
SYMBOLGEN: Macro variable _EGSOURCE resolves to SOURCE
MPRINT(_EG_RESTORENOTESANDSOURCE): options SOURCE;
31
32 filename output "\\path\Template_test2.xlsx";
33
34
35
36 %macro create_yourdata;
37 %do i=1 %to 1;*change for max obs;
38
39 data yourdata_&i (keep=index2 employee_number index value description is_total is_department_total is_title
39 ! is_projectable is_data_not_found resource_id kpi_set);
40 set new3;
41 if flag=&i;
42 run;
43
44 proc export data=yourdata_&i
45 file=output
46 dbms=XLSX replace;
47 Sheet='SAS_RANGE';
48 run;
49
50 %end;
51 options mprint symbolgen;
52 %mend create_yourdata;
53 %create_yourdata;
MPRINT(CREATE_YOURDATA): *change for max obs;
SYMBOLGEN: Macro variable I resolves to 1
MPRINT(CREATE_YOURDATA): data yourdata_1 (keep=index2 employee_number index value description is_total is_department_total
is_title is_projectable is_data_not_found resource_id kpi_set);
MPRINT(CREATE_YOURDATA): set new3;
SYMBOLGEN: Macro variable I resolves to 1
MPRINT(CREATE_YOURDATA): if flag=1;
MPRINT(CREATE_YOURDATA): run;
NOTE: There were 11133 observations read from the data set WORK.NEW3.
NOTE: The data set WORK.YOURDATA_1 has 226 observations and 12 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds
SYMBOLGEN: Macro variable I resolves to 1
MPRINT(CREATE_YOURDATA): proc export data=yourdata_1 file=output dbms=XLSX replace;
2 The SAS System 20:19 Thursday, September 21, 2017
MPRINT(CREATE_YOURDATA): WXLX;
MPRINT(CREATE_YOURDATA): Sheet='SAS_RANGE';
MPRINT(CREATE_YOURDATA): run;
NOTE: The export data set has 226 observations and 12 variables.
NOTE: "OUTPUT" file was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 0.39 seconds
cpu time 0.17 seconds
MPRINT(CREATE_YOURDATA): options mprint symbolgen;
54
55 %_eg_hidenotesandsource;
MPRINT(_EG_HIDENOTESANDSOURCE): options nonotes;
MPRINT(_EG_HIDENOTESANDSOURCE): options nosource;
SYMBOLGEN: Macro variable _EGNOTES resolves to NOTES
MPRINT(_EG_RESTORENOTESANDSOURCE): options NOTES;
SYMBOLGEN: Macro variable _EGSOURCE resolves to SOURCE
MPRINT(_EG_RESTORENOTESANDSOURCE): options SOURCE;
70
71
72 %_eg_hidenotesandsource;
MPRINT(_EG_HIDENOTESANDSOURCE): options nonotes;
MPRINT(_EG_HIDENOTESANDSOURCE): options nosource;
SYMBOLGEN: Macro variable _EGNOTES resolves to NOTES
MPRINT(_EG_RESTORENOTESANDSOURCE): options NOTES;
SYMBOLGEN: Macro variable _EGSOURCE resolves to SOURCE
MPRINT(_EG_RESTORENOTESANDSOURCE): options SOURCE;
75
log when using range
1 The SAS System 20:19 Thursday, September 21, 2017
1 %_eg_hidenotesandsource;
MPRINT(_EG_HIDENOTESANDSOURCE): options nonotes;
MPRINT(_EG_HIDENOTESANDSOURCE): options nosource;
SYMBOLGEN: Macro variable _EGNOTES resolves to NOTES
MPRINT(_EG_RESTORENOTESANDSOURCE): options NOTES;
SYMBOLGEN: Macro variable _EGSOURCE resolves to SOURCE
MPRINT(_EG_RESTORENOTESANDSOURCE): options SOURCE;
5 %_eg_hidenotesandsource;
MPRINT(_EG_HIDENOTESANDSOURCE): options nonotes;
MPRINT(_EG_HIDENOTESANDSOURCE): options nosource;
SYMBOLGEN: Macro variable SASWORKLOCATION resolves to ""
SYMBOLGEN: Macro variable _EGNOTES resolves to NOTES
MPRINT(_EG_RESTORENOTESANDSOURCE): options NOTES;
SYMBOLGEN: Macro variable _EGSOURCE resolves to SOURCE
MPRINT(_EG_RESTORENOTESANDSOURCE): options SOURCE;
31
32 filename output "Path\Template_test2.xlsx";
33
34
35
36 %macro create_yourdata;
37 %do i=1 %to 1;*change for max obs;
38
39 data yourdata_&i (keep=items;
40 set new3;
41 if flag=&i;
42 run;
43
44 proc export data=yourdata_&i
45 file=output
46 dbms=XLSX replace;
47 Range='SAS_RANGE';
48 run;
49
50 %end;
51 options mprint symbolgen;
52 %mend create_yourdata;
53 %create_yourdata;
MPRINT(CREATE_YOURDATA): *change for max obs;
SYMBOLGEN: Macro variable I resolves to 1
MPRINT(CREATE_YOURDATA): data yourdata_1 (keep=items);
MPRINT(CREATE_YOURDATA): set new3;
SYMBOLGEN: Macro variable I resolves to 1
MPRINT(CREATE_YOURDATA): if flag=1;
MPRINT(CREATE_YOURDATA): run;
NOTE: There were 11133 observations read from the data set WORK.NEW3.
NOTE: The data set WORK.YOURDATA_1 has 226 observations and 12 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
SYMBOLGEN: Macro variable I resolves to 1
MPRINT(CREATE_YOURDATA): proc export data=yourdata_1 file=output dbms=XLSX replace;
2 The SAS System 20:19 Thursday, September 21, 2017
MPRINT(CREATE_YOURDATA): WXLX;
NOTE: Line generated by the invoked macro "CREATE_YOURDATA".
53 file=output dbms=XLSX replace; Range='SAS_RANGE'; run;
_____
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
MPRINT(CREATE_YOURDATA): Range='SAS_RANGE';
NOTE: The previous statement has been deleted.
MPRINT(CREATE_YOURDATA): run;
NOTE: The export data set has 226 observations and 12 variables.
NOTE: "OUTPUT" file was successfully created.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 0.40 seconds
cpu time 0.18 seconds
MPRINT(CREATE_YOURDATA): options mprint symbolgen;
54
55 %_eg_hidenotesandsource;
MPRINT(_EG_HIDENOTESANDSOURCE): options nonotes;
MPRINT(_EG_HIDENOTESANDSOURCE): options nosource;
SYMBOLGEN: Macro variable _EGNOTES resolves to NOTES
MPRINT(_EG_RESTORENOTESANDSOURCE): options NOTES;
SYMBOLGEN: Macro variable _EGSOURCE resolves to SOURCE
MPRINT(_EG_RESTORENOTESANDSOURCE): options SOURCE;
70
71
72 %_eg_hidenotesandsource;
MPRINT(_EG_HIDENOTESANDSOURCE): options nonotes;
MPRINT(_EG_HIDENOTESANDSOURCE): options nosource;
SYMBOLGEN: Macro variable _EGNOTES resolves to NOTES
MPRINT(_EG_RESTORENOTESANDSOURCE): options NOTES;
SYMBOLGEN: Macro variable _EGSOURCE resolves to SOURCE
MPRINT(_EG_RESTORENOTESANDSOURCE): options SOURCE;
75
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.