How to export the data to the Excel range

[SASdevAnneMarie]

Hello Experts,

Do you know please some method to export the data to the particular range of Excel file?
The proc export doesn’t work with range statement. 😞
I would like to export the data and then to recalculate it with the Excel formulas.

Thank you very much!

[art297]

If your version of SAS is running on a Windows-based system, take a look at the exportxl macro. It can export to a specific range and can even capitalize on using another workbook as a template.

The code that is available at:

  https://github.com/art297/Papers/blob/master/Excelling_to_Another_Level_with_SAS/src/exportxl.sas

and the paper, powerpoint and tip sheet can be found at: 

 https://github.com/art297/Papers/tree/master/Excelling_to_Another_Level_with_SAS/doc

Art

[SASdevAnneMarie]

Hello Art,

Thank you for your suggestion.
Unfortunately, it doesn’t work for me, I’m using a distant server.

[art297]

What operating system is the server running?

[SASdevAnneMarie]

Windows 7

[art297]

Try running the macro but including the following parameter and value:

usenotepad=Y

Often, if running on a server, the server doesn't have direct access to your system's clipbrd. As such, the above mentioned parameter will use the server's copy of Notepad to mimic a copy/paste operation.

Let me/us know if that worked.

[SASdevAnneMarie]

Thank you Art,

I run: %exportxl(data=sashelp.class, outfile=c:\temp\class.xlsx,usenames=N,
sheet=Students, usenotepad=Y)
Unfortunately I have no results (no file), but I don't have the errors in the log.

[art297]

Out of curiosity, have you downloaded and compiled the macro before trying to use it?

Does your log provide any feedback?

[SASdevAnneMarie]

Yes, I did that but it doesn't work

[art297]

In order to know what is happening, I'd need to know what is written to your log. Do you know why you're not getting anything written to your log?

Out of curiosity, what version of SAS are you running?

[art297]

In your call to the macro you are setting the outfile to be:

c:\temp\class.xlsx

Does your server know what c:\temp is? I don't think so because it is trying to write to:

"',"\\laxxxx.com\LMPAPROD\USINE_PRIIPS\5. RETRAITEMENTS MANUELS\Chaine

Profils\4. Analyse\Marie_TEST\class1.xlsx",'"

As an outfile the macro needs to be supplied with a file that it can create.

[SASdevAnneMarie]

Yes, I used c:\temp\class.xlsx, I had no results, no file created, so I tried to write my outfile in « \\laxxxx.com... », I have the same: no results, no file created.
Thank you for your help

[art297]

I suggest you check with your IT department. They can configure your environment so that the server will recognize c: (or some other drive) that exists on your computer and which you can write to.

Once that is done, you should be able to use the macro.

[SASdevAnneMarie]

Hello Art,

Thank you for your mesage!
I don't think that it is possible.
There is no other SAS solutions?

Thank you!