Export to specific cells in Excel

[jlaw]

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.

[Haikuo]

DDE:

SAS(R) 9.2 Companion for Windows, Second Edition

[art297]

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;

[LeRoyBessler]

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.

[art297]

:  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

[Reeza]

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.

[SP_SAS]

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

[art297]

@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

[SP_SAS]

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.

[art297]

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

[jlaw]

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.

[Reeza]

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;

[art297]

: 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;

  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;