topic Re: How to export the data to the Excel range in SAS Programming

How to export the data to the Excel range

SASdevAnneMarie — Thu, 17 Sep 2020 18:28:47 GMT

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!

Re: How to export the data to the Excel range

art297 — Thu, 17 Sep 2020 18:49:56 GMT

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

Re: How to export the data to the Excel range

SASdevAnneMarie — Wed, 23 Sep 2020 16:30:34 GMT

Hello Art,

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

Re: How to export the data to the Excel range

art297 — Wed, 23 Sep 2020 16:48:04 GMT

What operating system is the server running?

Re: How to export the data to the Excel range

SASdevAnneMarie — Wed, 23 Sep 2020 17:02:53 GMT

Windows 7

Re: How to export the data to the Excel range

art297 — Wed, 23 Sep 2020 17:19:06 GMT

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.

Re: How to export the data to the Excel range

SASKiwi — Thu, 24 Sep 2020 06:35:53 GMT

@SASdevAnneMarie - You can specify a range if you use DBMS = EXCEL in your PROC IMPORT. Check out this doc link .

Re: How to export the data to the Excel range

SASdevAnneMarie — Thu, 24 Sep 2020 09:23:55 GMT

Thank you for your messge, SASKiwi.
Unfortunately, I'm looking for EXPORT.

Re: How to export the data to the Excel range

SASdevAnneMarie — Thu, 24 Sep 2020 09:41:29 GMT

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.

Re: How to export the data to the Excel range

art297 — Thu, 24 Sep 2020 13:06:17 GMT

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

Does your log provide any feedback?

Re: How to export the data to the Excel range

SASdevAnneMarie — Thu, 24 Sep 2020 13:22:52 GMT

Yes, I did that but it doesn't work

Re: How to export the data to the Excel range

art297 — Thu, 24 Sep 2020 13:49:11 GMT

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?

Re: How to export the data to the Excel range

SASdevAnneMarie — Mon, 28 Sep 2020 08:10:48 GMT

Re: How to export the data to the Excel range

art297 — Thu, 24 Sep 2020 17:17:47 GMT

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.

Re: How to export the data to the Excel range

SASdevAnneMarie — Thu, 24 Sep 2020 17:45:44 GMT

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

Re: How to export the data to the Excel range

art297 — Thu, 24 Sep 2020 17:54:32 GMT

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.

Re: How to export the data to the Excel range

SASdevAnneMarie — Fri, 25 Sep 2020 08:09:21 GMT

Hello Art,

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

Thank you!

Re: How to export the data to the Excel range

art297 — Fri, 25 Sep 2020 12:17:30 GMT

A first step would be to find out where you can actually create a file. Can you use proc export to create an Excel file of sashelp.class? If so, what specifically did you use as the path and name of the file?

Re: How to export the data to the Excel range

SASdevAnneMarie — Fri, 25 Sep 2020 12:47:01 GMT

Yes, I did that, my log is:

1 The SAS System 14:43 Friday, September 25, 2020

1 ;*';*";*/;quit;run;

2 OPTIONS PAGENO=MIN;

3 %LET _CLIENTTASKLABEL='Programme';

4 %LET _CLIENTPROCESSFLOWNAME='Flux de processus';

5 %LET _CLIENTPROJECTPATH='';

6 %LET _CLIENTPROJECTPATHHOST='';

7 %LET _CLIENTPROJECTNAME='';

8 %LET _SASPROGRAMFILE='';

9 %LET _SASPROGRAMFILEHOST='';

11 ODS _ALL_ CLOSE;

12 OPTIONS DEV=PNG;

13 GOPTIONS XPIXELS=0 YPIXELS=0;

15 GOPTIONS ACCESSIBLE;

16 Proc Export Data=sashelp.class

17 Outfile= "\\laxxxx.com\LMPAPROD\USINE_PRIIPS\5. RETRAITEMENTS MANUELS\Chaine Profils\4.

17 ! Analyse\Marie_TEST\Class_test.xlsx"

18 dbms=xlsx

19 replace;

21 quit;

NOTE: The export data set has 19 observations and 5 variables.

NOTE: "\\ laxxxx.com\LMPAPROD\USINE_PRS\5. RETRAITEMENTS MANUELS\Chaine Profils\4. Analyse\Marie_TEST\Class_test.xlsx" file

was successfully created.

NOTE: PROCEDURE EXPORT used (Total process time):

real time 0.30 seconds

cpu time 0.03 seconds

23 GOPTIONS NOACCESSIBLE;

24 %LET _CLIENTTASKLABEL=;

25 %LET _CLIENTPROCESSFLOWNAME=;

26 %LET _CLIENTPROJECTPATH=;

27 %LET _CLIENTPROJECTPATHHOST=;

28 %LET _CLIENTPROJECTNAME=;

29 %LET _SASPROGRAMFILE=;

30 %LET _SASPROGRAMFILEHOST=;

32 ;*';*";*/;quit;run;

33 ODS _ALL_ CLOSE;

36 QUIT; RUN;

Re: How to export the data to the Excel range

art297 — Fri, 25 Sep 2020 15:40:44 GMT

You can use range= with proc export if you use dbms=xlsx. However, as I recall, that will allow you to write to a range but not an existing range. I.e., you can't modify an existing worksheet that way.

I did notice a possible difference between your running proc export and running the macro. With proc export you enclosed the outfile with double quotes. You don't appear to have done that with the macro. Definitely worth a try .. i.e., enclosing the name supplied to the outfile parameter with double quotes.

You could always try a dde approach but that, of course, comes with all of the problems that has caused everyone to stop using dde.