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,
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?
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.
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
Yes, I am looking for all the options in the table till we move into total SAS based solutions.
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.
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;
And I've had circumstances where DDE is the only thing that worked
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.
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.
unfortunately not. Below is detail of folder you suggested. I am totally lost .
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
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 ?
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.