BookmarkSubscribeRSS Feed
Tmacfan
Calcite | Level 5

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

8 REPLIES 8
Kurt_Bremser
Super User

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?

Tmacfan
Calcite | Level 5

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Kurt_Bremser
Super User

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

Tmacfan
Calcite | Level 5

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

Patrick
Opal | Level 21

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

Cynthia_sas
SAS Super FREQ

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

Tmacfan
Calcite | Level 5

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

 

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 2930 views
  • 1 like
  • 5 in conversation