topic Re: export to excel in SAS Procedures

export to excel

yael — Thu, 21 Jul 2016 12:15:43 GMT

Hello

After receiving feedback here I found that the only way I can import excel file to SAS is by FILE-IMPOER DATA

Now I am asking about export to excel. When I open the FILE - EXPORT DATA I get the following window (attached).

Is there any way to save the output of RESULTS in Library WORK or SASUSER, because I tried with no success

Thanks for any help

Re: export to excel

RW9 — Thu, 21 Jul 2016 12:18:30 GMT

Yes, you just need to know the network path to those libnames. If you right click on Work in explorer, you will see the network area, copy paste that in and it will save it there - they are only directories somewhere. However, why would you want to? It doesn't make sense, Work is a temporary work area which gets removed at the end of the session. Sasuser is another SAS created folder - not for your use. Create a proper directory somewhere and save your files to there.

Re: export to excel

Kurt_Bremser — Thu, 21 Jul 2016 12:21:08 GMT

A LIBRARY is a place for SAS tables, views and catalogs. Excel files do not belong there. Store them somewhere else.

Putting them into the same place where a library exists only creates confusion.

Re: export to excel

ballardw — Thu, 21 Jul 2016 20:32:11 GMT

@yael wrote:

Now I am asking about export to excel. When I open the FILE - EXPORT DATA I get the following window (attached).

Is there any way to save the output of RESULTS in Library WORK or SASUSER, because I tried with no success

RESULTS do not go into any library.

To send things that end up in Results you would provide an ODS Destination for the procedures, the specific destination may depend on your needs. I typically use tagsets.Excelxp that creates XML readable by Excel for any non-graphic output.

Example:

ods tagsets.excelxp file="c:\path\example.xlsx"; /* though I normally save as xml name*/

proc print data=sashelp.class;

run;

ods tagsets.excelxp close;

put your preferred path to the folder you want in place of C:\path.

If your results are being generated as HTML you could use the File-Save as from the Results window to save as HTML and open with Excel.

You may have access to ODS EXCEL which would work similar to the tagsets.excelxp example.

Re: export to excel

yael — Sun, 24 Jul 2016 09:56:38 GMT

Hello @ballardw

Following your answer I tried the following code:

ods excel file='C:\Users\Hedvay\Documents\excel1.xlsx';

proc means data=sasuser.sasfile170716 mean median std min max;

var ROA;

class FIRMDEFI;

run;

ods excel close;

The answer I got in LOG window is:

ods excel file='C:\Users\Hedvay\Documents\excel1.xlsx';

-----

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

2 proc means data=sasuser.sasfile170716 mean median std min max;

3 var ROA;

4 class FIRMDEFI;

5 run;

NOTE: Writing HTML Body file: sashtml.htm

NOTE: There were 4814 observations read from the data set SASUSER.SASFILE170716.

NOTE: PROCEDURE MEANS used (Total process time):

real time 0.92 seconds

cpu time 0.79 seconds

6 ods excel close;

-----

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

That means that I can't use it in SAS?

Thanks for any feedback

Re: export to excel

RW9 — Sun, 24 Jul 2016 13:46:47 GMT

ods excel is a SAS v9.4 feature, if your not running that version, you won't have it. If you are running that, then check the statements before the ods for errors, unfinished quotes or macros etc. which could be causing further issues.