Export to Excel from unix SAS code

Reply
Occasional Contributor
Posts: 7

Export to Excel from unix SAS code

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

Esteemed Advisor
Posts: 6,646

Re: Export to Excel from unix SAS code

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?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 7

Re: Export to Excel from unix SAS code

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

Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Export to Excel from unix SAS code

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.

Esteemed Advisor
Posts: 6,646

Re: Export to Excel from unix SAS code


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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 7

Re: Export to Excel from unix SAS code

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

Respected Advisor
Posts: 3,825

Re: Export to Excel from unix SAS code

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

SAS Super FREQ
Posts: 8,717

Re: Export to Excel from unix SAS code

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

Occasional Contributor
Posts: 7

Re: Export to Excel from unix SAS code

Hi Cynthia,

 

Thank you for taking out the time and answering my queries Smiley Happy

 

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

 

 

 

Ask a Question
Discussion stats
  • 8 replies
  • 213 views
  • 1 like
  • 5 in conversation