topic Re: Export to Excel template - one per group in SAS Enterprise Guide

Export to Excel template - one per group

itchyeyeballs — Thu, 14 Sep 2017 16:57:13 GMT

Hi all,

I've had a look about but can't find the answer I need so hoping somone can point me in the right direction.

We have an Excel template that formats some output data (its pretty basic but the end users are used to the format and dont want to change).

I've discovered that the existing process involves someone creating a master data set in Base SAS, exporting results to a csv then spending hours copy/pasting rows into a sheet in the Excel template in order to create one final formatted output per target group/user

Is there a way to:

Get Enterprise Guide to loop through a list of targets (users - several hundred)
Filter the main data set to just appropriate results (approx 100 rows per user)
Copy the results to a specific worksheet in the excel template
(Ideally then hide the raw data worksheet in the excel workbook - I could live without this step)
Save the template with the user ID of the relevant user as part of the filename.
Repeat for rest of the target list

I want to end up with multiple Excel workbooks in a folder, each containing just the data for the relevant user.

Extra bonus points if you can tell me how EG could then email the work books to the right people (I have the email addresses in a seperate list)

I could do this using some VBA within Excel but it would be much better to wrap the whole process into a single EG project that can be run in one step.

Any help appreciated!

Re: Export to Excel template - one per group

Reeza — Thu, 14 Sep 2017 17:06:12 GMT

If the formatting is basic, I would instead consider moving everything to ODS EXCEL destination instead which can create a native XLSX file with formatted tables and graphs.

Otherwise, you can definitely do this, but its more coding. Do you have X commands enabled by chance?

Re: Export to Excel template - one per group

itchyeyeballs — Thu, 14 Sep 2017 17:15:43 GMT

Hi, not sure on the X commands (what are they?),

All our historic scripts that exported to Excel used DDE so we are starting friom scratch with EG.

The Excel template has a drop down list so the user can select a KPI which then populates a table (it uses an array formula to pick the right data based on the selection)

Re: Export to Excel template - one per group

TomKari — Thu, 14 Sep 2017 17:46:26 GMT

I think your idea is right-on. You should be able to save hours of work with reasonably minimal SAS coding, but it may get a bit complicated. Ask lots of questions!

Suggested process:

1. Get your process of:
   - filtering the main data
   - create the required worksheet
   - save the workbook in the relevant folder
   - email the workbook to the user

working for one user. All of these questions have been asked in the past, so there should be lots of advice available on how to do this.

2. Once you have it working for one user, you can use the SAS macro facility to perform the same process for your user list. Using SAS macros can be tough, but this is a pretty easy example.

I completely endorse @Reeza's suggestion to create your Excel output using the ODS EXCEL destination. Get your SAS version as up-to-date as possible, as this is a facility that is undergoing very rapid improvement from version to version.

I'm not sure why @Reeza is asking about the "x" command...I don't see a need for it at this point.

Good luck!
Tom

Re: Export to Excel template - one per group

Reeza — Thu, 14 Sep 2017 17:50:49 GMT

Ok, have you tried a straight PROC EXPORT into the Excel Template sheet? You can create a named range in the Excel file and export to that named range. The named range should be big enough to have all the data.

You can use FCOPY to copy the Excel file from one location to another with a different name.

Re: Export to Excel template - one per group

itchyeyeballs — Thu, 21 Sep 2017 17:52:08 GMT

Thank you for the suggestions guys,

I made some progress but havn't gotten all the way there, two issues holding me up

Proc Export doesnt seem be able to use pre-defined named ranges (it just creates a new sheet in the workbook) - I used the guide here - http://support.sas.com/kb/20/923.html

and this code

proc export data=yourdata2_&i
file=output
dbms=xlsx replace;
sheet='SAS_RANGE';
run;

Second issue, fcopy doesn't seem to want to create a new file, the docs seem to imply there needs to be an existing destination file? (I also had trouble getting it to run at all inside the macro)

Re: Export to Excel template - one per group

Reeza — Thu, 21 Sep 2017 19:01:36 GMT

@itchyeyeballs wrote:

Thank you for the suggestions guys,

I made some progress but havn't gotten all the way there, two issues holding me up

Proc Export doesnt seem be able to use pre-defined named ranges (it just creates a new sheet in the workbook) - I used the guide here - http://support.sas.com/kb/20/923.html

and this code
proc export data=yourdata2_&i
file=output
dbms=xlsx replace;
sheet='SAS_RANGE';
run;
Second issue, fcopy doesn't seem to want to create a new file, the docs seem to imply there needs to be an existing destination file? (I also had trouble getting it to run at all inside the macro)

What version of SAS do you have?

This behaviour doesn't sound correct. Can you post your full code and log.

Re: Export to Excel template - one per group

itchyeyeballs — Thu, 21 Sep 2017 20:09:12 GMT

HI,

I'm using Enterprise Guide 7.13 with MS Office 2016 64 bit.

My macro is

filename output "\\filepath\Template_test2.xlsx";

%macro create_yourdata;
 %do i=1 %to 1;*change for max obs;

data yourdata_&i (keep=items);
    set new3;
    if flag=&i;
run;

	proc export data=yourdata_&i
	file=output
	dbms=xlsx replace;
	sheet='SAS_RANGE';
	run;

 %end;
%mend create_yourdata;
%create_yourdata;

To start my Excel workbook has a tab called SAS_Data with a range defined as SAS_RANGE

After running the macro I get a new tab called SAS_RANGE with a new range defined within it called SAS_RANGE.

Re: Export to Excel template - one per group

Reeza — Thu, 21 Sep 2017 20:31:46 GMT

Have you tried with RANGE instead of SHEET since you're using a named range and not a sheet?

What version of SAS are you using? I know this wasn't possible in SAS 9.3

EDIT: Note that EG versions can have different SAS versions so we need the BASE SAS version info, not the EG version.

https://blogs.sas.com/content/sgf/2012/11/12/how-to-find-your-sas-version-and-components-list/

Re: Export to Excel template - one per group

itchyeyeballs — Fri, 22 Sep 2017 08:26:45 GMT

Hi,

BASE SAS version is 9.4 (TS1M4)

I have just tried 'range' but it threw an error, the technical notes say that I should be using a $ at the end of the name to determine a range or a sheet but I've tried both ways and no go. Its a shame as we have a load of old scripts that use DDE, I was hoping to update them if I could get this working.

Spent 3 hours copy pasting until midnight last night at least the project deadline was met.

Re: Export to Excel template - one per group

itchyeyeballs — Fri, 22 Sep 2017 09:06:09 GMT

Quick update:

I think I have worked out whats going wrong.

SAS seems to need the worksheet and the range to have the same name. It seems to delete the existing sheet and create a new one with a new named range. I was hoping it would write to a pre-existing range so that I could have multiple ranges on one sheet and also not need to have everything positioned starting at cell A1 (our templates are all hardcoded to look in specifiec locatons- we need to change this anyway but its going to be time consuming.)

If anyone has any other suggestions as to how I can write output to specifc cells and worksheets then I'd be really interested to test them out.

Re: Export to Excel template - one per group

Reeza — Fri, 22 Sep 2017 14:43:59 GMT

You need to have only one of SHEET or RANGE, if you have both you're going to get unexpected behaviour.

Re: Export to Excel template - one per group

itchyeyeballs — Fri, 22 Sep 2017 16:44:47 GMT

Hi,

Thank you for your patience with this,

The code I tried for range is below,

filename output "Path\Template_test2.xlsx";

%macro create_yourdata;
 %do i=1 %to 1;*change for max obs;

data yourdata_&i (keep=items);
    set new3;
    if flag=&i;
run;

	proc export data=yourdata_&i
	file=output
	dbms=XLSX replace;
	Range='SAS_DATA';
	run;

 %end;
%mend create_yourdata;
%create_yourdata;

Error is: ERROR 180-322: Statement is not valid or it is used out of proper order. (with Range highlighted)

I have tried several different approaches (a colleague has tried as well) all have given similar results, we can get EG to create a new named range but not populate an existing one. Must be something to do with our setup.

Re: Export to Excel template - one per group

Reeza — Fri, 22 Sep 2017 16:49:00 GMT

Post the full log, after running it with MPRINT and SYMBOLGEN options specified.

options mprint symbolgen;

Re: Export to Excel template - one per group

Reeza — Fri, 22 Sep 2017 16:53:23 GMT

Sorry... try using a libname instead. You also need to drop the range and then add the data in.

Does the following do what you want? I think the named range can also be case sensitive, so check what it shows up as when you assign the libname.

libname out xlsx "Path\Template_test2.xlsx";

data yourdata1 (keep=items);
    set new3;
    if flag=1;
run;

proc sql;
drop table out.SAS_DATA;
quit;

data out.SAS_DATA;
set yourdata1 ;
run;

libname out;

Re: Export to Excel template - one per group

itchyeyeballs — Fri, 22 Sep 2017 17:09:15 GMT

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

Re: Export to Excel template - one per group

itchyeyeballs — Fri, 22 Sep 2017 17:16:53 GMT

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

Re: Export to Excel template - one per group

Reeza — Fri, 22 Sep 2017 17:44:04 GMT

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/FareezaKhurshed-CoordinateComplexReports-Spring2014.pdf

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.

Re: Export to Excel template - one per group

itchyeyeballs — Fri, 22 Sep 2017 22:39:39 GMT

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.