Export to Excel template - one per group

[itchyeyeballs]

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!

[Reeza]

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? 

[itchyeyeballs]

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)

[TomKari]

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

[Reeza]

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. 

[itchyeyeballs]

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)

[Reeza]

---

@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.

[itchyeyeballs]

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.

[Reeza]

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/

[itchyeyeballs]

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.

[itchyeyeballs]

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.

[Reeza]

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

[itchyeyeballs]

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.

[Reeza]

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

options mprint symbolgen;

[Reeza]

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;