topic Re: updating excel sheets from tables in SAS in SAS Enterprise Guide

updating excel sheets from tables in SAS

learner_sas — Tue, 23 Jun 2015 14:08:19 GMT

Hello everybody,

I have 51 tables in SAS,which corresponds to 51 excel sheet tabs which has same standard formatting. Now I want to update all 51 sheet using those tables in SAS. I am wondering if anybody has done same thing on the past. Links to research papers or blogs would be great help since I want to understand the process throughly.

Thank you,

Re: updating excel sheets from tables in SAS

ballardw — Tue, 23 Jun 2015 15:29:46 GMT

What do you mean by "update". Replace existing values in specific cells? Add Rows? Add Columns? More than one of these?

And if this is a report type document: why Excel?

Re: updating excel sheets from tables in SAS

learner_sas — Tue, 23 Jun 2015 15:48:12 GMT

yes. Replacing existing values on specific cells.Why Excel, it was done in traditionally. I am pushing to create report in in SAS and export it. Hope I will get there in some time.

Re: updating excel sheets from tables in SAS

TomKari — Tue, 23 Jun 2015 16:05:46 GMT

It would be very easy to replace entire workbooks from SAS, instead of trying to update individual cells. Would this be a possibility instead?

Tom

Re: updating excel sheets from tables in SAS

learner_sas — Tue, 23 Jun 2015 18:51:06 GMT

Yes, I am looking for all the options in the table till we move into total SAS based solutions.

Re: updating excel sheets from tables in SAS

Reeza — Tue, 23 Jun 2015 19:01:51 GMT

There are a variety of methods, none are perfect and depend exactly on your situation and reporting needs.

My go to standard is to export to a workbook and have the formatted pages linked to the data export. Then I can export and replace that SAS dataset at will and the new reports update.

A second option, when I need to explicitly place values in specific locations and don't necessarily know the size of the table is either a macro written by Art/Fried Egg Poor/Rich Man's Proc Export

A third option, considered antiquated, is DDE to explicitly write to cells in Excel

Other options are to explicitly create reports using ODS Tagsets or in SAS 9.4 ODS EXCEL. The formatting options for these are quite advanced so depending on your report switching all to SAS may be relatively easy.

Re: updating excel sheets from tables in SAS

learner_sas — Tue, 23 Jun 2015 19:13:16 GMT

Reeza, I am starting with Poor/Rich Macro. I will update on on my result. I am refraining from DDE since it gave me problem on my test run;

Re: updating excel sheets from tables in SAS

Reeza — Tue, 23 Jun 2015 19:14:40 GMT

And I've had circumstances where DDE is the only thing that worked

Re: updating excel sheets from tables in SAS

learner_sas — Fri, 26 Jun 2015 18:46:20 GMT

Hello Finally I ran code of poor/rich. Here is what I did. reference macro was used from http://www.sascommunity.org/mwiki/images/f/f4/1793-2014.sas

1) I ran program from EG I got "undermined I/O faliure.

2) I tried from Base SAS and got no error. searched log there is no "error" word in whole log. But I can not see my file in the my computer. I have SAS on my PC not on server. Below is the code and log I submitted. Clearly it says whole program is fine but I can not see the output file

My code best on poor/rich macro:

%exportxl( data=sashelp.cars ,

outfile=C:\Users\myname\Desktop\test.xlsx,

type=N,

usenames=Y,

sheet = test);

My log:

DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

NOTE: There were 1 observations read from the data set SASHELP.CARS.

NOTE: The data set WORK.T_E_M_P has 1 observations and 15 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

NOTE: There were 1 observations read from the data set WORK.T_E_M_P.

NOTE: The data set WORK.T_E_M_P has 1 observations and 15 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

NOTE: The file CODE2INC is:

Filename=C:\Users\myname\AppData\Local\Temp\SAS Temporary

Files\_TD5640_ISC83676_\#LN00032,

RECFM=V,LRECL=256,File Size (bytes)=0,

Last Modified=26Jun2015:14:37:39,

Create Time=26Jun2015:14:37:39

NOTE: 30 records were written to the file CODE2INC.

The minimum record length was 15.

The maximum record length was 55.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

NOTE: There were 428 observations read from the data set SASHELP.CARS.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

NOTE: The file DUMMY1 is:

Filename=C:\Users\myname\AppData\Local\Temp\SAS Temporary

Files\_TD5640_ISC83676_\PasteIt.vbs,

RECFM=V,LRECL=512,File Size (bytes)=0,

Last Modified=26Jun2015:14:37:39,

Create Time=26Jun2015:14:37:39

NOTE: 23 records were written to the file DUMMY1.

The minimum record length was 5.

The maximum record length was 70.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

NOTE: DATA statement used (Total process time):

real time 0.28 seconds

cpu time 0.04 seconds

Everything looks right still I am not able to see my written file. Am I missing anything?

Thank you for your time.

Re: updating excel sheets from tables in SAS

ballardw — Fri, 26 Jun 2015 19:11:07 GMT

It looks like your output is in the folder

C:\Users\myname\AppData\Local\Temp\SAS Temporary Files\_TD5640_ISC83676_\

Have you used something like Windows explorer to look in that folder? One of them has the exciting name of #LN00032 .

When running a macro and getting unexpected results it sometimes help diagnose issues by turning on

Options mprint symbolgen mlogic;

before execution.

Re: updating excel sheets from tables in SAS

learner_sas — Fri, 26 Jun 2015 19:21:37 GMT

unfortunately not. Below is detail of folder you suggested. I am totally lost .

Re: updating excel sheets from tables in SAS

Reeza — Fri, 26 Jun 2015 19:21:51 GMT

It works perfectly for me, putting the file in the correctly indicated location.

What version of Windows are you using? You should also see Excel pop up and the data exported - at least I do

Re: updating excel sheets from tables in SAS

learner_sas — Fri, 26 Jun 2015 19:24:51 GMT

I can see command-like popup on my screen. I have Excel 2010, SAS 9.3 and Windows 7 on my computer. It is getting mysterious. Any thoughts ?

Re: updating excel sheets from tables in SAS

Reeza — Fri, 26 Jun 2015 19:29:05 GMT

Open the VBS file - using a text editor. Delete the last 3 lines, the close/quit, and run it. It should leave the excel file open and you can see what happens. It should also indicate where the file is being saved. Don't copy/paste anything between originally running the step and this step.

Re: updating excel sheets from tables in SAS

learner_sas — Fri, 26 Jun 2015 19:38:44 GMT

No luck again,

it is showing the exact same location. I tried .xlsx and .xls both and it did not work. I also ran by deleting bottom three rows. It still closes my Excel application.

Dim objExcel

Dim Newbook

Dim NewSheet

Dim inSheetCount

Set objExcel = CreateObject("Excel.Application")

Set Newbook = objExcel.Workbooks.Add()

objExcel.Visible = True

inSheetCount = Newbook.Application.Worksheets.Count

set NewSheet = Newbook.Sheets.Add( ,objExcel.WorkSheets(inSheetCount))

objExcel.DisplayAlerts = False

i = inSheetCount

Do Until i = 0

Newbook.Worksheets(i).Delete

i = i - 1

Loop

Newbook.Sheets(1).Name="one"

Newbook.Sheets("one").Select

Newbook.Sheets("one").Range("A1").Activate

Newbook.Sheets("one").Paste

NewSheet.SaveAs("C:\Users\myname\Desktop\sastest.xls")

objExcel.Workbooks.Close

objExcel.DisplayAlerts = True

objExcel.Quit

Re: updating excel sheets from tables in SAS

Reeza — Fri, 26 Jun 2015 19:40:53 GMT

You deleted the three bottom lines, saved the script, ran it and it closed your Excel file? Did you see any data paste in?

Re: updating excel sheets from tables in SAS

ballardw — Fri, 26 Jun 2015 19:45:06 GMT

With the path C:\Users\myname\Desktop\ do you actually have "myname" as part of the path on your drive?

Re: updating excel sheets from tables in SAS

learner_sas — Fri, 26 Jun 2015 19:55:39 GMT

I have my name first middle and last name. I just thought I should not give my name.

Re: updating excel sheets from tables in SAS

learner_sas — Fri, 26 Jun 2015 19:56:58 GMT

Yes . When I ran the program I see Command-like screen for a while and it closes itself after removing last three line as you suggested.

Re: updating excel sheets from tables in SAS

Reeza — Fri, 26 Jun 2015 19:58:28 GMT

You don't see Excel though?