topic Export to specific cells in Excel in SAS Programming

Export to specific cells in Excel

jlaw — Fri, 19 Jul 2013 22:49:59 GMT

Need some direction or ideas etc.

I would like to export data to specific cells in an excel preformatted workbook.

Does anyone have any ideas how to do this?

Thank you in advance.

Re: Export to specific cells in Excel

Haikuo — Sat, 20 Jul 2013 00:20:28 GMT

DDE:

SAS(R) 9.2 Companion for Windows, Second Edition

Re: Export to specific cells in Excel

art297 — Sat, 20 Jul 2013 13:43:49 GMT

If you simply want to copy and paste the data, you can do it very easily in two steps: (1) use a datastep to put the data on your system's clipboard and (2) manually paste (i.e., ctrl-v) the clipboard where you want in Excel.

If you don't need the variables names the datastep can be as simple as:

%let lib=sashelp;

%let mem=class;

filename clippy clipbrd;

data _null_;

file clippy dsd dlm="09"x;

set &lib..&mem.;

put(_all_)(:);

run;

filename clippy clear;

Re: Export to specific cells in Excel

LeRoyBessler — Sat, 20 Jul 2013 20:23:49 GMT

I do not see how your DATA step exports a specific SAS data variable value to a specific Excel worksheet cell.

Implicit in the reference to DDE in the response by Hai.kuo is the fact that DDE can do it.

Re: Export to specific cells in Excel

art297 — Sat, 20 Jul 2013 23:20:19 GMT

: You and I apparently read the OP's 's request differently. When I read "export data to specific cells in an excel preformatted workbook" I interpret the question as possibly simply being a request to export a SAS dataset in a way that it can be pasted in an existing, preformatted Excel workbook. As far as I know, Excel's paste or paste special can accomplish that task if the data reside in one's clipboard. My code was simply one method of getting the data into the clipboard.

Yes, of course, the same task can be accomplished using DDE but, as long as the OP doesn't need to create a fully automated procedure, I think using a datastep with the clipbrd method is a lot easier.

Art

Re: Export to specific cells in Excel

Reeza — Sat, 20 Jul 2013 23:43:06 GMT

Create a named range of the cells in the Excel workbook.

Create the dataset in the same format the range is in.

Export out using proc export with the range specified as the name range in the excel workbook OR

use libname and export out to the specified range.

Personally, I export to a separate sheet and link the cells from that sheet to my desired cells.

Re: Export to specific cells in Excel

jlaw — Tue, 23 Jul 2013 19:17:37 GMT

To clarify - Ideally this will be an automated process.

I have heard of it being done just have not seen any examples of the process.

I will review the various suggestions and let you know if I am successful or have additional questions.

Feel free to respond with additional options/methods ect.

thank you all so much for your replies.

Re: Export to specific cells in Excel

Reeza — Tue, 23 Jul 2013 19:49:29 GMT

Here's the steps I used to automate my reports. You can copy the template for each report needed and then name them using a macro variable. The workbook in this case was .xlsm because there was a macro in the workbook that was used (via DDE) to open the workbook and export it to PDF reports in a later step.

Step 1: First define your ranges where you want the tables to go. Try to name your ranges SAS compliant names (less than 32 characters, doesn’t start with a number/symbol) and not the same as the tab names in the worksheet.

http://www.dummies.com/how-to/content/how-to-name-a-cell-or-range-in-excel-2010.html

Step1a: Copy the workbook

%sysexec copy

"C:\Temp\Template_v48.xlsm"

"C:\Temp\Output\&file_name..xlsm";

Step 2: Link to workbook

libname sample excel "C:\Temp\Output\&file_name..xlsm" ;

Step 3: Clear the ranges to remove any old data (QA Step)

proc sql;

drop table sample.table1;

drop table sample.table2;

drop table sample.table3;

drop table sample.table4;

quit;

Step 4: Export data to worksheets

data sample.table1;

set table1;

run;

data sample.table2;

set table2;

run;

data sample.table3;

set table4;

run;

data sample.table4;

set table4;

run;

Step 5: Close workbook

libname sample;

Re: Export to specific cells in Excel

art297 — Tue, 23 Jul 2013 22:38:29 GMT

: You can also automate the code I suggested by combining it with 's suggestion of using DDE. e.g.:

%let lib=sashelp;

%let mem=class;

filename clippy clipbrd;

data _null_;

file clippy dsd dlm="09"x;

set &lib..&mem.;

put(_all_)(:);

run;

filename clippy clear;

options noxsync noxwait xmin;

filename sas2xl dde 'excel|system';

data _null_;

length fid rc start stop time 8;

fid=fopen('sas2xl','s');

if (fid le 0) then do;

rc=system('start excel');

start=datetime();

stop=start+10;

do while (fid le 0);

fid=fopen('sas2xl','s');

time=datetime();

if (time ge stop) then fid=1;

end;

rc=fclose(fid);

run;

data _null_;

file sas2xl;

put '[open("c:\art\template")]';

put '[SELECT("R2C1")]';

put '[PASTE()]';

put '[error(false)]';

put'[save.as("'"c:\art\&mem."'")]';

put '[file.close(false)]';

put '[quit()]';

run;

Re: Export to specific cells in Excel

SP_SAS — Mon, 27 Mar 2017 15:37:24 GMT

Hi there,

Can you please send me a sample code to do it this way.

--I understand the problems is to send the dataset values in a specified named range in excel.

Thanks,

Santosh

Re: Export to specific cells in Excel

art297 — Mon, 27 Mar 2017 16:03:11 GMT

@SP_SAS: Sending data to a named range is easy. The problem is when one wants to export to a range that isn't predefined.

Somehow, I think the post you replied to (from 4 years ago) motivated @Tom, @FriedEgg and me to write a macro to accomplish such tasks. Take a look at: http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export

Art

Re: Export to specific cells in Excel

SP_SAS — Mon, 27 Mar 2017 16:57:23 GMT

I am trying to do this on Linux.

Thanks. Can you also share with me how to "Sending data to a named range is easy" not using DDE.

Re: Export to specific cells in Excel

art297 — Mon, 27 Mar 2017 17:09:24 GMT

A solution, including references to a couple of papers describing it and alternatives, can be found at:

http://stackoverflow.com/questions/32578013/exporting-sas-dataset-to-excel-named-ranges