topic Re: Export to Excel from unix SAS code in SAS Programming

Export to Excel from unix SAS code

Tmacfan — Mon, 16 Jan 2017 07:07:24 GMT

Greetings Everyone!!,

I am afraid I know already know the answer to this one but is there any possible way to export to Excel from unix SAS code(not PC SAS).

Basically I want a piece of code which I can run on Unix SAS server using nohup and then automate it thorugh SAS BI server.

Best,

Vikas

Re: Export to Excel from unix SAS code

Kurt_Bremser — Mon, 16 Jan 2017 07:32:38 GMT

Depends on what you want as output (raw data or formatted report). Beginning with SAS 9.4, you can write xlxs-formatted files directly, using proc export, ODS, or libname.

Prior to that, the best method was to use a text-based format for data transfer, or ods tagsets.excelxp for formatted reporting in a XML file.

So:

- what SAS version do you have?

- what is the desired output?

Re: Export to Excel from unix SAS code

Tmacfan — Mon, 16 Jan 2017 09:07:44 GMT

Hi Kurt,

Thank you for taking out the time to address my SAS problem.

Basically what I am trying is

1) copy the one excel with pivot to SAS temp using below,

data _null_;
length filein 8 fileid 8;
filein = fopen('in','I',1,'B');
fileid = fopen('out','O',1,'B');
rec = '20'x;
do while(fread(filein)=0);
rc = fget(filein,rec,1);
rc = fput(fileid, rec);
rc =fwrite(fileid);
end;
rc = fclose(filein);
rc = fclose(fileid);
run;

2) Now I want to updated another raw data sheet in the above excel with pivot so that

when user opens the excel pivot gets auto refreshed with new raw data

For this I was wondering if I can export sas dataset to this excel.

Best,

Vikas

Re: Export to Excel from unix SAS code

RW9 — Mon, 16 Jan 2017 09:12:48 GMT

The problem you face is common, your trying to update another applications file without the tools. Reverse the thinking of it. If you absolutely have to use Excel (which I would always push back on), then use the tools available in Office - VBA. You have a complex non-standard file format, and to that you need to adde some exported data - forget about what tech - so as a soloution, look at a way of loading data into the Excel file directly from the Excel file. It is very simple, dump out your data to CSV. Then in your Excel file has a few simple lines of VBA code which opens that CSV file into a new sheet, then updates all the graphs and pivots, there are many examplew on the web of loading CSV files in Excel VBA, and note you don't need to store the VBA in the same file, you could have any empty Excel file with your VBA code which loads the template XLSX and the CSV, puts them together and then saves them. Avoids all the copy paste, and trying to drive one app from another.

Re: Export to Excel from unix SAS code

Kurt_Bremser — Mon, 16 Jan 2017 09:15:43 GMT

@Tmacfan wrote:

For this I was wondering if I can export sas dataset to this excel.

Replacing a single sheet within an Excel xlsx workbook would be done by assigning the Excel as a library with libname xlsx and then writing a dataset to it.

Requires SAS 9.4.

Re: Export to Excel from unix SAS code

Tmacfan — Mon, 16 Jan 2017 10:41:36 GMT

Thanks Kurt, so there is no way to do the same in SAS 9.3?

Re: Export to Excel from unix SAS code

Patrick — Mon, 16 Jan 2017 11:22:39 GMT

The XLSX engine makes things much simpler. It's fully available since SAS 9.3 TS1M1.

http://support.sas.com/kb/51/580.html

There are also options with other engines as documented here (i.e. page 8):

https://support.sas.com/documentation/cdl/en/acpcref/63181/PDF/default/acpcref.pdf

Re: Export to Excel from unix SAS code

Cynthia_sas — Mon, 16 Jan 2017 15:12:14 GMT

Hi:

You indicate that you have the SAS BI Server. Do you also have the SAS Add-in for Microsoft Office. If you do have and use the SAS Add-in for Microsoft Office, then you can load SAS data into a worksheet via a SAS task or a SAS Stored Process and have the sheet automatically be updated with the SAS data when the workbook/sheet is opened.

I do not understand why you are not using the capabilities build into the BI server, stored process and the SAS Add-in for Microsoft Office. One of the reasons for getting the BI components and the SAS Add-in for Microsoft Office is to be able to use SAS data without familiar Office applications and to make the capability to "refresh" on open just another option to check. Here's a paper that describes the basic SAS Add-in functionality http://support.sas.com/resources/papers/proceedings14/1403-2014.pdf

And, if you do not want to use the built-in capabilities, you can always write VBA, as described in this paper http://support.sas.com/resources/papers/proceedings11/012-2011.pdf

cynthia

Re: Export to Excel from unix SAS code

Tmacfan — Tue, 17 Jan 2017 09:20:53 GMT

Hi Cynthia,

Thank you for taking out the time and answering my queries 🙂

I do have SAS addin capability but for this particlualr task I need to update raw data into a excel sheet with pivot already existing

and then somehow mail that to end-user.

I will look into VBA if that can help me.

Appreciate your help o t he same.

Best,

Vikas