topic Re: Exporting SAS dataset into specific cells in SAS Programming

Exporting SAS dataset into specific cells

Shawnty — Fri, 06 Oct 2017 09:33:33 GMT

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!

Re: Exporting SAS dataset into specific cells

Shawnty — Fri, 06 Oct 2017 09:43:40 GMT

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;

Re: Exporting SAS dataset into specific cells

RW9 — Fri, 06 Oct 2017 09:53:53 GMT

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.

Re: Exporting SAS dataset into specific cells

Shawnty — Fri, 06 Oct 2017 10:39:39 GMT

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

Re: Exporting SAS dataset into specific cells

RW9 — Fri, 06 Oct 2017 10:43:55 GMT

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.

Re: Exporting SAS dataset into specific cells

Shawnty — Fri, 06 Oct 2017 10:48:52 GMT

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.

Re: Exporting SAS dataset into specific cells

Kurt_Bremser — Fri, 06 Oct 2017 10:49:50 GMT

@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.

Re: Exporting SAS dataset into specific cells

Shawnty — Fri, 06 Oct 2017 10:50:53 GMT

Hi KurtBremser,

Not sure what you meant by that.

Re: Exporting SAS dataset into specific cells

RW9 — Fri, 06 Oct 2017 10:58:07 GMT

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.

Re: Exporting SAS dataset into specific cells

Shawnty — Fri, 06 Oct 2017 11:01:27 GMT

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.

Re: Exporting SAS dataset into specific cells

Kurt_Bremser — Fri, 06 Oct 2017 11:44:23 GMT

@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.

Re: Exporting SAS dataset into specific cells

Shawnty — Fri, 06 Oct 2017 13:41:06 GMT

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

Re: Exporting SAS dataset into specific cells

RW9 — Fri, 06 Oct 2017 13:51:54 GMT

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.

Re: Exporting SAS dataset into specific cells

Shawnty — Fri, 06 Oct 2017 13:55:30 GMT

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

Re: Exporting SAS dataset into specific cells

art297 — Fri, 06 Oct 2017 21:35:27 GMT

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

Re: Exporting SAS dataset into specific cells

Shawnty — Fri, 06 Oct 2017 21:40:50 GMT

Hi art297,

Thank you very much for responding.

Im afraid my knowledge of server/systems are a bit weak. Would you mind explaining a bit deeper o what you mean by able to run system commands and server environment?

Thanks on advance, J

Re: Exporting SAS dataset into specific cells

art297 — Fri, 06 Oct 2017 21:58:40 GMT

They're two different issues.

As for the server question, when you run SAS is it running a copy of SAS that exists on your own computer, or one that exists on another machine (i..e, a server).

If it's the latter, the macro will only work if that other machine (the server) is windows based (i.e., can run windows programs and languages like vb script) and notepad.

And, if it is running on a server, the macro has a parameter called system_path. When one calls the macro in such an environment, the system+path parameter has to have a value of some directory on the server for which you have write access (i.e., can write and save files to).

As for the system commands, some SAS interfaces (like EG) don't let run issue system commands (like the x command or call system, i.e., commands that let you run external programs (like notepad, excel or word) directly from SAS.

Art, CEO, AnalystFinder.com

Re: Exporting SAS dataset into specific cells

Shawnty — Fri, 06 Oct 2017 22:06:12 GMT

Interesting, thanks for making it clear. I wont be back to work until Monday. I will have a look at it once im back. Again, thank you!

Re: Exporting SAS dataset into specific cells

art297 — Thu, 12 Oct 2017 19:31:49 GMT

@Shawnty: Did you get the macro to work on your server?

Art, CEO, AnalystFinder.com

Re: Exporting SAS dataset into specific cells

Shawnty — Thu, 12 Oct 2017 21:55:35 GMT

Hi Art297,

We decided to go with VBA instead of SAS. So there is no need for it.