BookmarkSubscribeRSS Feed
learner_sas
Quartz | Level 8

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,

25 REPLIES 25
ballardw
Super User

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?

learner_sas
Quartz | Level 8

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.

TomKari
Onyx | Level 15

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

learner_sas
Quartz | Level 8

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

Reeza
Super User

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.

learner_sas
Quartz | Level 8

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;

Reeza
Super User

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

learner_sas
Quartz | Level 8

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.

ballardw
Super User

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.

learner_sas
Quartz | Level 8

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

snip_tempsas.PNG

Reeza
Super User

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

learner_sas
Quartz | Level 8

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 ?

Reeza
Super User

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.

learner_sas
Quartz | Level 8

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

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!

SAS Enterprise Guide vs. SAS Studio

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.

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
  • 25 replies
  • 2509 views
  • 0 likes
  • 4 in conversation