BookmarkSubscribeRSS Feed
MandyYuan
Calcite | Level 5

Hello guys,

Does anyone know how to put SAS data (like two numbers) into specific cells (for example, Q5:Q6) in Excel sheet without using dde command because it does not work in Enterprise Guide. This is for reporting purpose and I want to automate the report generating process. I know cope and paste would work, but I just want to see if I can fully utilize the functions in SAS.

I know you can create a excell libname reference, like:

libname something EXCEL "location/filename.xlsx";

And it can create a new Excel sheet or create a name-range for the data, but I haven't found ways to put data into specific cells using this method.

Thank you,

Mandy

5 REPLIES 5
Ksharp
Super User

If I was right, you could put data into specific cells.

libname something EXCEL "location/filename.xlsx" scantext=no ;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

TBH your not generating a report from SAS, just doing a couple of numbers.  The report is already there is you just want to copy the two numbers over.  Easiest way is just to copy and paste.  You could export the two values and have Excel load the file and place the values where you like using a VBA macro, but for the sake of two numbers is it worth it?

Maybe also think about generating your whole report from SAS (preferably not using Excel at all).

bmillson1
Fluorite | Level 6

Hi Mandy,

 

Did you ever figure-out a way to populate specific cells in an excel file using SAS/EG?

 

I receive 100 monthly excel files already pre-populated with data, and I need to add 5 numbers in specific locations in the existing files. DDE would do the trick but don't have it, copy/paste is not efficient.

 

If I could only figure-out how to push data to the specific cells in the excel files using SAS/EG,...,

 

Will keep working at it but if you found a solution, would be great to hear from you.

 

Thanks,

Bart

DWilson
Pyrite | Level 9

I'm switching from using SAS on my local PC running Windows 7 to using SAS enterprise guide locally with SAS grid installed on a remote server running Linux so will be losing DDE support.

 

I am investigating replacing DDE functionality by calling R via SAS' system command and using functions in the xlConnect package. The R xlConnect package appears to allow users to specify where to place data in an excel spreadsheet; as well as providing lots of other controls on formatting in Excel and such.

 

I currently have a set of SAS macros that I use to invoke DDE and the idea is to replace the guts of the SAS macros with R functions (maybe submitted via IML) that replace the DDE functionality. This means that I won't have to modify my SAS programs substantially to work with SAS Enteprise Guide and SAS grid.

 

There are benefits to this:

-Hundreds of legacy programs don't need to be re-written

-Don't need to go and learn VBA ( I personally think R is more accessible)

-Don't need to invert the relationship between SAS and Excel for producing tables: SAS pushing data to Excel versus Excel pulling data from SAS

-SAS logs for QC/auditing purposes

 

Anyway, I'm still working through this and will be glad to share my findings at the end.

 

DWilson
Pyrite | Level 9

Here's an update:

 

We've successfully migrated our existing DDE macros to use SAS/IML to call R to mimic DDE functionality. We have two versions of our macros; each using a different R package to create excel files: Openxlsx and xlConnect.  They each have different features; one does better with graphics, the other does better with cell formatting, that sort of thing.

 

Lessons learned:

 

If creating an excel worksheet with lots of numbers/characters placed into different cells, it helps substantially to collate requests to population various cells within the SAS program and then call R one time with all of the various requests passed at a single time. Naively calling R each time a value or set of values have been created will substantially impact the time it takes to create an Excel file due to the, relatively, large overhead required by invoking R and having the R packages open the excel file.

 

Comment:

I'm still surprised that SAS hasn't addressed this by providing a native functionality to write to individual cells in an Excel spreadsheet. After all, I'm pretty sure that figuring out the XML scheme behind Excel files should be doable as evidenced by the work of the R package creators. I'm also sure that SAS could figure out a way to delay writing to Excel in order to avoid I/o overhead. One possibility would be to write to memory instead of an excel file directly and then once the last record in a data step was processed, save the data in memory to an excel file.  Anyway, it's still on my wish list for SAS to invest some modicum of resources into supporting this feature.

 

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 5 replies
  • 1557 views
  • 1 like
  • 5 in conversation