topic Re: export to excel in SAS Procedures

export to excel

hdg — Fri, 28 Mar 2014 15:35:39 GMT

Hi I have a quick question on exporting to excel

I already have an existing template stored C:\temp\template.xlsx - having one sheet called 'Template'

I have soem datasets of same columns that I want to export to this template

Say I have a dataset called N1 and another dataset called N2 I would like to

1) Creat an excel file called New which makes a copy of the tab 'Template'

2) Outputs the dataset N1 into New and renames the tab as N1

3) Do the same thing for the N2 dataset

In the end New should have two tabs N1 and N2 as the same format as the template file

Thanks for your help!

Re: export to excel

art297 — Fri, 28 Mar 2014 15:57:35 GMT

I won't be able to look at this until later this afternoon but, if no one else has provided an answer by then, I will look at it. Simple answer, yes, it probably can be done. But I would need to know the answers to a few questions:

what version of SAS are you using?

what operating system are you running on?

how big are the files (i.e., # of records and # of variables)?

do you need to copy the variable names or labels or neither?

should the data be copied starting at cell A1 or to a specific range?

Re: export to excel

hdg — Fri, 28 Mar 2014 16:06:38 GMT

Thanks so much for your help Arthur.

Here is what you have asked.

I am using SAS 9.2 TS Level2M3 W32_VSPRO platform

OS Windows Version 6.1.7601

Number of records can vary [about 5000 rows] and columns are fixed [24 columns]

I would need to copy the column names - like the regular proc export command copies over the labels

starting at A1

Here is what I have been trying:a simple example just to copy the information in N1. This doesnt work. This does not have the extra part where I am trying to have N2 as well copied in the same file..

x to make a copy over the existing template

%let newfile=C:\temp\Template.xlsx;

%let newfiled = C:\temp\New.xlsx;

%let sheetName = N1;

options noxwait noxsync;

X copy "&newfile" "&newfiled" ;

proc export data= N1 outfile="&newfiled" dbms=EXCEL ; sheet=&sheetName;

run;

Re: export to excel

art297 — Sat, 29 Mar 2014 15:37:35 GMT

I think that I almost have an answer, but that depends on one more answer from you. Does your template contain formulas or just formatting, highlighting and the like?

Re: export to excel

hdg — Sat, 29 Mar 2014 19:25:59 GMT

IIt has only formatting and charts created that reference the data that is outfitted starting on cell a1

Thanks v much!!

Re: export to excel

art297 — Sat, 29 Mar 2014 22:43:53 GMT

Here is the best I can do without charging you my exorbitant consulting fee and, even then, I'm probably too busy right now to even accept such a contract.

Tom Abernathy, FriedEgg and I just presented a paper, at SAS Global Forum, that provides a macro which has the capabilities needed to automate some aspects of what you are trying to accomplish. That is, being able to modify an existing worksheet by pasting new data into it.

I just quickly wrote an additional macro that may be all that is needed to accomplish your current task.

Both macros, and a description of the original, can be found and downloaded at:

A Poor/Rich SAS Users Proc Export - sasCommunity

Here is the code I just ran to test the combination of the two macros. It assumes I have a file called "d:\art\template.xlsx" that has one worksheet called "Template" and that I want to use all of the formatting, etc. in that template, twice, to create a new Workbook called "d:\art\tclass.xlsx" that will have two worksheets called "N1" and "N2".

First, I create the datasets that I want to use to populate the new workbook:

proc sort data=sashelp.class out=class1;

by name;

run;

proc sort data=sashelp.class out=class2;

by descending name;

run;

Then, I ran the following to create the new Workbook. Basically, it copies the range (from A1 thru E21) from the Template worksheet in d:\art\template.xlsx TO a new worksheet (N1) in a new Workbook (:d:\art\tclass.xlsx 😞

%copytemplate(template=d:\art\template.xlsx,

templatesheet=Template,

range=A1:E21,

outfile=d:\art\tclass.xlsx,

sheet=N1,

type=N)

Then, the following code is run to copy the N1 worksheet to a new worksheet called N2:

%copytemplate(template=d:\art\tclass.xlsx,

templatesheet=N1,

range=A1:E21,

outfile=d:\art\tclass.xlsx,

sheet=N2,

type=A)

Then, the following code is run to copy class1 to the N1 Worksheet:

%exportxl(data=class1,

outfile=d:\art\tclass.xlsx,

sheet=N1,

type=M,

usenames=Y,

range=A1,

replace=Y)

And, finally, the following code is run to copy class2 to the N2 Worksheet:

%exportxl(data=class2,

outfile=d:\art\tclass.xlsx,

sheet=N2,

type=M,

usenames=Y,

range=A1,

replace=Y)

The above method will copy everything (graphs, formulas, and formatting) from the template to both of the new worksheets.

The only thing I wasn't able to get it to do was correctly populate the graphs, as they copy with absolute reference to the original worksheet (i.e., template). If you, or anyone, knows how to get around that limitation (regarding charts), I'd be glad to incorporate it into the code.

Art

Re: export to excel

hdg — Sat, 29 Mar 2014 23:53:40 GMT

Thanks Art. really appreciate your kind help on this. Will work on this tomorrow and let you know how it works out. thanks for the note as well. Heena

Re: export to excel

art297 — Sun, 30 Mar 2014 16:21:28 GMT

While I already posted a partial solution, I think I have figured out a way to do it that will keep all graphs and formatting. I'll let you know as soon as I've written and tested it.

Re: export to excel

Reeza — Sun, 30 Mar 2014 17:38:24 GMT

If N1/N2 will always be the same table length then Link the graphs/formatting to two named ranges, N1 and N2.

SAS can export to the named ranges directly, and then the charts/format will be updated with the new data

Re: export to excel

art297 — Mon, 31 Mar 2014 01:30:34 GMT

I ended up modifying the original exportxl macro which can be downloaded from:

A Poor/Rich SAS Users Proc Export - sasCommunity

To accomplish what you want to do, save your template as an Excel template. I saved mine as d:\art\template.xltx).

Then, to test the idea, I created two sasfiles:

proc sort data=sashelp.class out=class1;

by name;

run;

proc sort data=sashelp.class out=class2;

by descending name;

run;

Then, to create a new workbook, called d:\art\tclass.xlsx, using sheet Template in d:\art\template.xltx and adding the data from class1 into a sheet called N1, I submitted the following call of the macro:

%exportxl(data=class1,

template=d:\art\template.xltx,

templatesheet=Template,

outfile=d:\art\tclass.xlsx,

sheet=N1,

type=N,

usenames=Y,

range=A1,

replace=Y)

Finally, to add class2 to the d:\art\tclass.xlsx workbook as sheet N2, again using the Template worksheet in d:\art\template.xltx, I submitted the following code:

%exportxl(data=class2,

template=d:\art\template.xltx,

templatesheet=Template,

outfile=d:\art\tclass.xlsx,

sheet=N2,

type=A,

usenames=Y,

range=A1,

replace=Y)

I hope to update the documentation to explain the new named parameters, and how the code works, later this week.

The critical new parameters are: template, templatesheet, Type should be set to equal N to create a new workbook, and to A to add a new worksheet to an existing workbook.

Art

Re: export to excel

hdg — Sat, 17 May 2014 04:12:41 GMT

Hi Arthur, Just wanted to let you know the exportxl macro is awesome! Thanks soooo much for your help. You are amazing! Heena

Re: export to excel

art297 — Sat, 17 May 2014 21:58:56 GMT

Glad to hear that it worked out. And thank you for giving my colleagues and me good ideas for improving the utility of the macro. BTW, you might want to mark this question as "answered" so that others don't spend any unnecessary time trying to come up with solutions (unless, of course, they have a better solution that hasn't yet been proposed).