Export to Excel template - one per group

[itchyeyeballs]

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         

[Reeza]

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:

https://www.sas.com/content/dam/SAS/en_ca/User%20Group%20Presentations/Edmonton-User-Group/FareezaKh...

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. 

[itchyeyeballs]

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. 

[itchyeyeballs]

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