Exporting SAS dataset into specific cells

[Shawnty]

Hello!

Im working with SAS 9.2 and i need to export a big dataset into a structured MASTER excel template  with a huge amount of rows and columns with a given structure.

My first approach was to use DDE which worked great as i could allocate to specific cells. But for some reason the DDE crashes on some specific cells for an unknown reason. Therefore this method can not be used.

The excel template itself is structured with a lot of formulas and formats and as such i can not import the document to SAS - input the data needed and be rewrite the document/sheets. I need to write to specific cells just as the DDE approach.

To my knowledge this can be done through libname excel and write to a named range. Again, this can not be done either as im writing to a MASTER where we can not change the data itself.

So to summarize my question. Is there a method in SAS 9.2 such as DDE that can write data from a SAS dataset to a specific cell /range of cells in excel without overwriting everything around the specific cells or including more formats in the MASTER such as a named range? If you happen to have such a method, please throw in an example as well as im new to SAS!

Thanks on advance!

[Shawnty]

Here is an example of a DDE code which worked out well until EXCEL crash 😕

data rea_num;
input a b c d e;
datalines;
0.01 0.001 0.25252 10 150
;
run;

filename out dde "excel|CSV_CR_REA!r2c1:r2c5";

data _null_;
       set rea_num;
       file out;

      format a PERCENT8.2 

      b PERCENT10.2
      c PERCENT5.2;

     put a '09'x b '09'x c '09'x d '09'x e '09'x;

run; 

[RW9 · Accepted solution]

There is this:

http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export

However if you are using Excel that much then I wouldn't bother with SAS at all.  Export your data as CSV, then write some VBA code - doesn't have to be in the same Excel doc, just code which loads your master, loads the CSV, and then maps the data into the main Spreadsheet.  Its pretty straight forward but outside the realm of this forum.

Personally I would avoid Excel as much as possible, really poor data format.  DDE is ancient and may not support most things.

This sentence:

"To my knowledge this can be done through libname excel and write to a named range. Again, this can not be done either as im writing to a MASTER where we can not change the data itself."

I do not know what you mean here, if you can't write to the Excel file, then there is no method of getting data in there using any technology.

[Shawnty]

Hi RW9,

Ill look into proc export. Wasn't aware it could handle specific ranges. This report is made once every 2 years and as part of this years project i'm creating automatic population for the Template. It's also a requirement from external parties (regulations) and such can't be avoided.

As for the quote: What i meant is: from what i've seen libname excel can do two things:
Write to a excel document/sheet.

Write to a named range.

Where creating a named range requires me to format the master, and write to sheet would write over all the formats included in the master. This is however just my understanding of the module libname excel.

Ive considered the CSV... Can be created in a similar fashion as DDE but as for now ill try to avoid VBA due to keeping the code simple and constricted to one language. 

Thanks for the advice, J

[RW9]

The link I posted isn't proc export, proc export is not what you want.  I don't believe there is a direct export method in the way you want.  Export to CSV and write VBA (which is what all Office apps are in the background) and do it that way.  As for keeping the code constricted to one language, you are constricted to use Excel, VBA is Excel, therefore the constriction is on using Excel in the first place.

[Shawnty]

Oh okay, ill dig deeper into it. 

As for the language. What i meant is i dont want others to be required to learn SAS and VBA to populate the template, rather just looked at my code as a benchmark and change where requires need be.

I found this written by you earlier:

"

First recommendation, don't use proc import/export - they are guessing procedures.

Second point, what exactly is in the spreadsheet your trying to change, Excel is not a good tool for anything, but especially data.   With SAS you *could* use DDE (which is very old tech and not recommended), you could libname excel to the file, and set cell values there.  However if I really had to do this (and I would really not be happy with using Excel for anything other than management toy), I would export the values to be updated into a CSV file from SAS.  Then in the spreadsheet I wanted to change I would write some VBA code to open the CSV, and copy the data items to specific cells.  VBA is very simple and there are plenty of examples online for doing this."

Would you mind expanding what you meant by setting cell values? Cause i was not aware that was possible as i wrote earlier.

[Shawnty]

Would you mind expanding what you meant by setting cell values? Cause i was not aware that was possible as i wrote earlier.

[RW9]

DDE is a M$ technology to interact with Office files, its is 20years old now and not supported.  Its possible that it can write a value into a cell, however by your own conditions you would need to know how Excel works, what the language is for DDE etc. which is a language in itself (and what my linked article should show).  From my side, I do not support DDE, its far too old, doesn't work half the time.  Try the given macro in that link.  Other than that I don't believe there is a way from SAS - a strongly structured data based language - to interact directly with an unstructured file format such as Excel with using one of the linking tools such as VBA or DDE.

[Shawnty]

It has been a long dag of frustration. I misread your quote as if it were for libname, not DDE.

Thanks a lot for the help RW9. Ill dig deeper into your link.

[Kurt_Bremser]

---

@Shawnty wrote:
.... and constricted to one language. 

---

If you want to achieve  that, can the crappy Excel and do all your logic in SAS. No sense riding a kids tricycle when you can have a Hummer.

[Shawnty]

Hi KurtBremser,

Not sure what you meant by that.

[Kurt_Bremser]

---

@Shawnty wrote:
Hi KurtBremser,

Not sure what you meant by that.

---

Simply, Excel is not a sensible tool for doing business intelligence. It lacks features for documentability, allows data errors without complaining, has natural limits on data size, and on and on and on.

I personally never employ Excel for any mission-critical task or for data that exceeds a few screens in size (because you will loose control one day). We have SAS for that. Every step in the analytics chain is documented by logs, program changes are kept in a source control system, and so on.

[Shawnty]

Hi RW9,

Dont know how to contact you other than continuing this post.

Ive tried the macro you linked to me but it does not seem to be working for me on SAS 9.2.

Ive downloaded the macro and ran the first examples, but immediately tries to create a work.T_e_m_p instead of the outfile given. And if ive understood this correctly, the code does so incase something goes wrong.

Have you encountered this problem yourself, and if you havent, is there any chance you could run the macro example 1 or 2 and see if it creates the file as it should?

/J

[RW9]

Yes, just ran the first one on my machine (9.4) and it doesn't produce any file.  Unfortunately it is not code from me, though there is a contact in the PDF.  I think it really highlights how much of a pain using Excel is, that code uses DDE, but also create a VB script as well behind the scene to do various things.  As I mention before, if after really throwing the toys out of the cradle I ever have use Excel, I would just default to using VBA.

[Shawnty]

Okay, thank you for confirming the problem. VBA is something ive avoided like the plague. Guess it's time to go in head first and learn about VBA.

/J

[art297]

As one of the co-authors of http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export (along with @Tom and @FriedEgg ), I can assure you that the macro doesn't use DDE.

It will only work on machines that can both run vb script and system commands (like x).

Also, if you're running it from a server environment, the server has to be able to run vb script and system commands, and the server_path parameter has to be included (i.e., 

a path, on the server, for which you have write access (e.g., S:\temp) )

Art, CEO, AnalystFinder.com