BookmarkSubscribeRSS Feed
jlaw
Calcite | Level 5

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.

12 REPLIES 12
art297
Opal | Level 21

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
Obsidian | Level 7

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
Opal | Level 21

:  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
Super User

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
Obsidian | Level 7

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
Opal | Level 21

@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
Obsidian | Level 7

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
Opal | Level 21

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
Calcite | Level 5

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.Smiley Happy

Reeza
Super User

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
Opal | Level 21

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

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 12 replies
  • 18385 views
  • 6 likes
  • 6 in conversation