Desktop productivity for business analysts and programmers

updating excel sheets from tables in SAS

Reply
Frequent Contributor
Posts: 89

updating excel sheets from tables in SAS

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,

Super User
Posts: 11,114

Re: updating excel sheets from tables in SAS

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?

Frequent Contributor
Posts: 89

Re: updating excel sheets from tables in SAS

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.

PROC Star
Posts: 1,146

Re: updating excel sheets from tables in SAS

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

Frequent Contributor
Posts: 89

Re: updating excel sheets from tables in SAS

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

Super User
Posts: 19,058

Re: updating excel sheets from tables in SAS

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.

Frequent Contributor
Posts: 89

Re: updating excel sheets from tables in SAS

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;

Super User
Posts: 19,058

Re: updating excel sheets from tables in SAS

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

Frequent Contributor
Posts: 89

Re: updating excel sheets from tables in SAS

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.

Super User
Posts: 11,114

Re: updating excel sheets from tables in SAS

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.

Frequent Contributor
Posts: 89

Re: updating excel sheets from tables in SAS

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

snip_tempsas.PNG

Super User
Posts: 19,058

Re: updating excel sheets from tables in SAS

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

Frequent Contributor
Posts: 89

Re: updating excel sheets from tables in SAS

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 ?

Super User
Posts: 19,058

Re: updating excel sheets from tables in SAS

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.

Frequent Contributor
Posts: 89

Re: updating excel sheets from tables in SAS

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

Ask a Question
Discussion stats
  • 25 replies
  • 885 views
  • 0 likes
  • 4 in conversation