topic Re: How to export from SAS to Excel? in SAS Enterprise Guide

How to export from SAS to Excel?

dianalves — Wed, 04 Sep 2013 13:30:03 GMT

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

Re: How to export from SAS to Excel?

ChrisHemedinger — Wed, 04 Sep 2013 13:43:00 GMT

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

Re: How to export from SAS to Excel?

jakarman — Wed, 04 Sep 2013 13:44:19 GMT

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?

Re: How to export from SAS to Excel?

fbatch — Thu, 05 Sep 2013 09:27:43 GMT

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.

Re: How to export from SAS to Excel?

dianalves — Thu, 05 Sep 2013 17:46:19 GMT

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

Re: How to export from SAS to Excel?

jakarman — Thu, 05 Sep 2013 18:01:43 GMT

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.

Re: How to export from SAS to Excel?

sas_9 — Thu, 05 Sep 2013 20:55:14 GMT

/*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!

Re: How to export from SAS to Excel?

SteveNZ — Fri, 06 Sep 2013 00:14:26 GMT

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.

Download and install Chris@SAS custom tasks and the copy files to/from server from his SAS for Dummys website
Create a folder on your server for xml files
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
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
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
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;

Re: How to export from SAS to Excel?

jakarman — Fri, 06 Sep 2013 08:51:01 GMT

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.

Re: How to export from SAS to Excel?

dianalves — Fri, 06 Sep 2013 09:45:43 GMT

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

Re: How to export from SAS to Excel?

sunilzood — Fri, 06 Sep 2013 13:04:21 GMT

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;

Re: How to export from SAS to Excel?

80sMetalForever — Thu, 12 Sep 2013 20:32:34 GMT

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

Re: How to export from SAS to Excel?

SASKiwi — Fri, 13 Sep 2013 02:46:55 GMT

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.

Re: How to export from SAS to Excel?

80sMetalForever — Fri, 13 Sep 2013 13:01:50 GMT

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.

Re: How to export from SAS to Excel?

ChrisHemedinger — Fri, 13 Sep 2013 13:12:32 GMT

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

Re: How to export from SAS to Excel?

80sMetalForever — Fri, 13 Sep 2013 13:23:00 GMT

"Then use the Copy Files task (which you will need to download/install) to copy the result to the desired location on your PC. "

thx Chris - so does this mean one doesn't generally export straight from code on a VM to a 'PC'/network location? I was trying to get this all in code without the extra Copy Files step and share with co-workers, but if that's how it is then that's how it is. Appreciate the response.

Re: How to export from SAS to Excel?

ChrisHemedinger — Fri, 13 Sep 2013 13:30:41 GMT

Dear Mötley Crüe fan,

If you know of a location on your UNIX file system that you can:

(a) write files to, and

(b) access from the PCs on your network, perhaps via a network shared drive or Samba mount

Then you can accomplish this all with code. But you will need to know of a valid UNIX path and include that as the output file location in your program.

The technique I shared can work without you having to know anything special about your UNIX setup, but it does require an EG step to copy the result to where you want it.

Chris

Re: How to export from SAS to Excel?

80sMetalForever — Fri, 13 Sep 2013 13:38:02 GMT

ok I will ask an Admin for more info then...most/all co-workers run EG from their own computers when they need to do this but I'd like to 'solve' being able to do this on the VM. Thanks for the insight.