BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hdg
Obsidian | Level 7 hdg
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

View solution in original post

11 REPLIES 11
art297
Opal | Level 21

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?

hdg
Obsidian | Level 7 hdg
Obsidian | Level 7

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;

art297
Opal | Level 21

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?

hdg
Obsidian | Level 7 hdg
Obsidian | Level 7

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

Thanks v much!!

art297
Opal | Level 21

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

hdg
Obsidian | Level 7 hdg
Obsidian | Level 7

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

Reeza
Super User

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

art297
Opal | Level 21

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.

art297
Opal | Level 21

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

hdg
Obsidian | Level 7 hdg
Obsidian | Level 7

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

art297
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 2610 views
  • 1 like
  • 3 in conversation