topic Re: How proc export to a particular row and column in Excel? in SAS Procedures

How proc export to a particular row and column in Excel?

TashaBee — Mon, 13 Jul 2015 13:31:41 GMT

I was wondering if there is a way to use proc export to place data in a particular row and column in Excel. I know that proc import can access a row and column, but I have not found anything for proc export.

Re: How proc export to a particular row and column in Excel?

RW9 — Mon, 13 Jul 2015 13:43:15 GMT

First recommendation, don't use proc import/export - they are guessing procedures.

Second point, what exactly is in the spreadsheet your trying to change, Excel is not a good tool for anything, but especially data. With SAS you *could* use DDE (which is very old tech and not recommended), you could libname excel to the file, and set cell values there. However if I really had to do this (and I would really not be happy with using Excel for anything other than management toy), I would export the values to be updated into a CSV file from SAS. Then in the spreadsheet I wanted to change I would write some VBA code to open the CSV, and copy the data items to specific cells. VBA is very simple and there are plenty of examples online for doing this.

Re: How proc export to a particular row and column in Excel?

TashaBee — Mon, 13 Jul 2015 13:48:08 GMT

Simply, I wanted to places tables next to each other. Nothing major. But, if this is complex, I have something else lined up. I just wondered if it was possible.

Thanks,

Re: How proc export to a particular row and column in Excel?

RW9 — Mon, 13 Jul 2015 13:54:29 GMT

Ah, well that's different. Use tagsets.Excelxp:

ods tagsets.excelxp file="xyz.xml" options=(sheet_interval="none" sheet_name="mydata");

proc report data=xyx...;

proc report data=zyz...;

ods tagsets.excelxp close;

Re: How proc export to a particular row and column in Excel?

Reeza — Mon, 13 Jul 2015 14:57:32 GMT

There isn't an easy way, but there are other ways to get your data to excel and have it look presentable.

If you absolutely need to link to specific cells, VB is a good way to go.

ODS TAGSETS and EXCEL are two options.

Base SAS: Demo: ExcelXP Tagset and Microsoft Excel

Re: How proc export to a particular row and column in Excel?

TashaBee — Mon, 13 Jul 2015 15:04:32 GMT

The tables are stacked. Below is the concept that I am looking at. I guess I will have to rely on VB. I was hoping that it would be possible in SAS.

Re: How proc export to a particular row and column in Excel?

Reeza — Mon, 13 Jul 2015 15:12:24 GMT

See panel option in ODS MSOFFICE2K

Base SAS: The MSOffice2K_x Tagset Adds Options to the MSOffice2K Tagset

Re: How proc export to a particular row and column in Excel?

TashaBee — Mon, 13 Jul 2015 15:14:41 GMT

Thank you!!

Re: How proc export to a particular row and column in Excel?

Ksharp — Tue, 14 Jul 2015 13:28:59 GMT

Libname + excel and change the cell via data step, just like it is a sas dataset ?