How to export from SAS to Excel?

[dianalves]

Hello

I'm a new user of SAS Guide and need a little help.

My problem: I want to export a table from SAS to Excel with code. I have tried differente forms but nothing works. I read something about this in forum and someone said that is need a license to export, this is true?

I tried this forms:

proc export

  data=work.impw

  dbms=xls

  outfile="D:\Documents and Settings\p057132\Desktop\back.xls"

  replace;

run;

Here, nothing happens!

_____________//______________

ods Tagsets.ExcelXP file='D:\Documents and Settings\p057132\Desktop\test.xls';

title 'Listing of Class data';

proc print data=work.back;

run;

ods Tagsets.ExcelXP close;

Gives a strange output, I can't open it

_________________________-//___________________

I have tried other ones. I have a Enterprise Guide 4 version.

I hope you can help me.

Thank you,

Diana

[ChrisHemedinger]

Diana,

Here is a review of several methods.  Since you are running EG 4.1 and SAS 9.1.3, some of these options are not available to you.

http://blogs.sas.com/content/sasdummy/2012/02/11/export-excel-methods/

You should look at ODS CSV and Tagsets.ExcelXP.  However, EG 4.1 doesn't automatically handle that output for you.  If you want Excel to open the ExcelXP output without complaint, try naming the file with an XML file extension.

Chris

[dianalves]

Hi Chris,

I have EG 1.4 and SAS 9.132.0.0

I tried this:

ODS TAGSETS.EXCELXP

file='D:\Documents and Settings\p057132\Desktop\regression.xls'

STYLE=minimal;

proc reg data = WORK.QUERY145;

  model log_yt = log_yt_m1 log_yt_m2;

run;

quit;

ods tagsets.excelxp close;

quit;

ods tagsets.excelxp close;

It creates a file in SAS ( I just see it in Project Explorer), with "/SASSoft/MinerGuide/Lev1/SASMain/D:\Documents and Settings\p057132\Desktop\test.xml" extension. I can not open it.

ods csv body="D:\Documents and Settings\p057132\Desktop\regression.csv";

   proc print data=WORK.QUERY145;

   run;

   ods csv close;

It creates a csv file in SAS like above and not works with xlm.

In reality I would like to export the database to specifique folder in SAS server but is has to be in xls or csv (rather).

[jakarman]

How do I export from SAS to Excel files: Let me count the ways - The SAS Dummy

The ODS tagsets does not require a seperate license. An attention point is there are more versions of it , to be installed by your support.

The PROC Import/Export are requiring the ACCESS/PC files license running on your server machine.

What is your server type Windows / Unix. Who/what is your support for that?   

[fbatch]

You can use

FILENAME EXCEL "name_of_file.xls" ;

DATA _NULL_;

  set your_dataset ;

  FILE EXCEL lrecl=25000 ;

  if _n_ = 1 then do ;

   PUT 'column1' '09'x 'column2' '09'x 'Column3' '09'x  ...............'Columnn' '09'x ;

  end;

PUT column1 '09'x column2 '09'x column3 '09'x ......... columnn '09'x ;

RUN ;

Use the numxwd. format to format your numeric values.

[jakarman]

Of course you cannot open it,  it has been placed at the Unix/server side.  "/SASSoft/MinerGuide/Lev1/SASMain/....

Amazing you can place files there as it is the system configuration of the sas admin and a generic name (SASMain).

Be Lucky not having harmed anything.

You should have placed the file in a personal location and than download it. Or have it transfered to a SharePoint using a url,

Eguide can download the files also.  
Having it in the Windows environment than Excel can open it.   

[sas_9]

/*hi dianalves i have ran your insital code*/

proc export

  data=work.impw

  dbms=xls

  outfile="D:\Documents and Settings\p057132\Desktop\back.xls"

  replace;

run;

/(which works fine under me - i am using sas eg 4.1 with sas 9.1.3...check cris note if you are missing any compoenents...*/ ---if still error then share error message...

gd lk!

[dianalves]

I have ran this code but nothing, neither give error!I'm not ADMIN and perhaps I have troubles because of that. I'll try all option you gave me. Thank you a lot, I'll share all steps

[sunilzood]

TRY THIS :

Data Math; input Name $ Sirname $ Marks; cards;

Sunil K 24

Sanjeev Z 19

Rahul c 13

;run;

Data ENG; input Name $ Sirname $ Marks; cards;

Vishal K 24

Vicky Z 19

Meenu c 13

;run;

libname sample excel 'C:\SAS_DUMP\myexcel.xls';

data sample.math;  set Math;

data sample.eng;    set Eng; run;

libname sample;

[SteveNZ]

Hiya,

The process I use with EG is the following - its a pain to set up but you only have to do it once. The excelxp tagset gives you a lot of control over output especially if you have custom formats.

1. Download and install Chris@SAS custom tasks and the copy files to/from server from his SAS for Dummys website
2. Create a folder on your server for xml files
3. Create XML files in your code using the tagsets.excelxp (as many as you want) as well as the vbs script included below into the folder from step 2 - the vbs script will convert ALL your xml files to native Excel ones once downloaded
4. Use the copy files task you've installed to get your xml files and the vbs file (use *.* in the dialogue to get both xml and vbs files) from the folder in step 2 to a local folder you have set up
5. Use the 'System Command' task from Chris@Sas to start the vbs file off eg: "C:\MYLOCAL\convert.vbs" (no quotes and filename must be in dos format ie longer names abbreviated with ~1). The vbs file is set to self-destruct
6. I then have another step that deletes the xml files and vb script on the server as you no longer need them

The code for the vb script I use is below. It takes all xml files in the folder and converts them to excel so use with care... I have Excel 2010, if you have another version you may have to change the xlNormal number from 51 to your version

*vb script to convert xml and then self destruct (delete);

data _null_ ;

file "/YOURSERVERFOLDER/convert.vbs" ;

put "Dim xlApp, xlWkb, SourceFolder, TargetFolder, file" ;

put "Set xlApp = CreateObject(""excel.application"") " ;

put "set fs = CreateObject(""Scripting.FileSystemObject"")" ;

put "Const xlNormal=51" ;

put "SourceFolder=""C:\MYLOCAL"" " ;

put "xlApp.DisplayAlerts = False " ;

put "xlApp.Visible = false" ;

put "for each file in fs.GetFolder(SourceFolder).files" ;

put "If Right(LCase(file.Name), 4) = "".xml"" Then";

put " Set xlWkb = xlApp.Workbooks.Open(file)" ;

put " BaseName = fs.getbasename(file)" ;

put " FullTargetPath = SourceFolder & ""\"" & BaseName" ;

put " xlWkb.SaveAs FullTargetPath, xlNormal" ;

put " xlWkb.close" ;

put "end if" ;

put "next" ;

put "fs.DeleteFile(""C:\MYLOCAL\*.xml""), DeleteReadOnly";

put "xlApp.DisplayAlerts = true" ;

put "Set xlWkb = Nothing" ;

put "xlapp=Quit";

put "Set xlApp = Nothing" ;

put "fs.DeleteFile(Wscript.ScriptFullName)" ;

put "Set fs = Nothing" ;

run;

[jakarman]

Eguide is hiding many thing of the server side.

As you are using Unix (seeing your namings) some things to know of that is needed.

The easy ones:

- There is not letter for drives (D:) and the backslash \  is the other / forwardslash.

- the "my_documents" loction is the "$home" or just "~" at Unix.

A difficult one:

- the owner of the data or directorie is defnining the security / access rights.

- The owner is the creator of the file directory, When you create a file that is you.

This is quite differenet to used procedures where a dedciated person as Security administrator is doing all that kind of work.

Using Unix you are the security administrator. I know that is confusing, very confusing.  

The WS server with Eguide is normally pointing to your "home" in that place you can definine logical links to other locations having your data.

The logical link defnition is a Unix command so you have to execute that directly of by using it wiht a SAS-program using the XCMD facility

Your SAS paltform admin can define directories and some default filenames (aside libname) for you.

These are being acttivated by changes in the user_mod files in .  "/SASSoft/MinerGuide/Lev1/SASMain/... 

With that approach it is becoming more easy. The Unix mapping are becoming visible in Eguide file explorer in the same way as the Windows explorer.

[80sMetalForever]

Was this ever solved? I'm trying to do the same (on a virtual machine).

I run:

proc export

  data=testdata.aug31

  dbms=xls

  outfile="C:\Temp\test.xls"

  replace;

run;

And I get:

ERROR: Insufficient authorization to access /sas/LM92/Lev1/SASMain/C:\Temp\test.xls.

I am also new to SAS and I'm assuming it's being run on Unix judging by the path, so I'm not sure how to reference a file path to save the data to.

thanks for any insight; I'm using EG 4.3

[SASKiwi]

You are trying to write an Excel file to the C:\Temp directory of your SAS server which of course would not exist on Unix.

Try writing to a directory that you are allowed to from your server - perhaps where you store your SAS datasets? For Unix you need to use /subdir1/subdir2 naming convention.

[80sMetalForever]

Thx, when I remove the path altogether I get:

ERROR: Insufficient authorization to access /sas/LM92/Lev1/SASMain/test.xls.

How would I be able to find where my datasets are stored and/or what paths I have access to? Also should I be able to write to a network location? I verified I have access via the VM but again not sure what the syntax would be to export to that location.

If it helps when I look at the properties of a library I have access to I see:

Server: SASMain

Engine: BASE

Location: /Shared Data/TESTDATA

I did try to export to that locale but received ERROR: Physical file does not exist, /shared data/testdata/test.xls.

[ChrisHemedinger]

You should be able to use a variation of the technique that I shared here:

Export and download any file from SAS Enterprise Guide - The SAS Dummy

Your code would look like:

%let download_from = %sysfunc(getoption(work))/test.xls;

filename src "&download_from.";

proc export

  data=testdata.aug31

  dbms=xls

  outfile=src

  replace;

run;

Then use the Copy Files task (which you will need to download/install) to copy the result to the desired location on your PC.  Specify &download_from as the source location to download, and check the "Resolve SAS macro variables" option in the task to allow the task to find it.

Chris